Cq query combining dates and "does not contain" clause

[chetanvajre2014]

So we need a query to find users whose last modified date is earlier than say 2015/06/01 AND whose email address (property in profile) does not contain say "@mydomain.com" in their email address field

Current query (without the AND is). Can you suggest how to add the AND (DOES NOT CONTAIN)

path=/home/users
nodename=profile
daterange.property=cq:lastModified
daterange.upperBound=2015-06-06
orderby=@cq:lastModified

[smacdonald2008]

See this StackOverflow thread - this may help:  

http://stackoverflow.com/questions/22510025/how-do-i-add-a-where-not-to-a-querybuilder-query

[Amit_Kumar]

path=/home/users
nodename=profile
group.p.and = true
group.1_group.1_daterange.property=cq:lastModified group.1_group.1_daterange.upperBound=2001-06-06
group.2_group.p.not= true
group.2_group.1_property=email
group.2_group.1_property.1_value = %mydomain.com
group.2_group.1_property.operation = like

[kautuk_sahni]

Hi 

Please look at this post:-

You can use group predicate to create logical condetion in you query. you can create complex condetion using OR, AND & NOT operators in different groups.

e.g. 

path=/home/users type=rep:User

group.1_daterange.property=jcr:created group.1_daterange.lowerBound=2014-08-18 group.1_daterange.upperBound=2014-08-19

group.2_daterange.property=cq:lastModified group.2_daterange.lowerBound=2014-08-18 group.2_daterange.upperBound=2014-08-19

group.p.or=true

Link:- http://stackoverflow.com/questions/34047045/excluding-tags-from-results-using-querybuilder/34052962

//Excluding tags from results using Querybuilder

path=/content/some/path/you/need type=cq:Page group.1_fulltext=marketing:interest/product group.1_fulltext.relPath=jcr:content/@cq:tags group.p.not=true

what correpsonds to next xpath query:

/jcr:root/content/some/path/you/need//element(*, cq:Page) [ not(jcr:contains (jcr:content/@cq:tags, 'marketing:interest/product')c) ].

Also you can add p.limit=[some number] to get more than 10 results.

Must Read :- http://hashimkhan.in/2015/12/02/query-builder/

I hope this would work.

Thanks and Regards

Kautuk Sahni

[chetanvajre2014]

Amit

The query doesn't include the first clause at all. For whatever reason, the first group is being ignored

see generated xpath

/jcr:root/home/users//*
[
not(((jcr:like(@email, '%mydomain.org'))))
and fn:name() = 'profile'
]
order by @email

[chetanvajre2014 · Accepted solution]

Got this working. Following was the query

path=/home/users
 nodename=profile
daterange.property=cq:lastModified    
daterange.upperBound=2015-06-06
 group.2_group.p.not= true
 group.2_group.1_property=email
 group.2_group.1_property.1_value = %domain.org
 group.2_group.1_property.operation = like
p.limit=-1
orderby=@email

[Amit_Kumar]

Hi Chetan,

Thanks for letting me know about this. I missed that grouping doesn't work with Date Range predicates.

I didn't tested that it was just to give you a direction to work on .

Regards,

Amit