Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Cq query combining dates and "does not contain" clause

chetanvajre2014
Level 5
Level 5

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

1 Accepted Solution
chetanvajre2014
Correct answer by
Level 5
Level 5

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

View solution in original post

0 Replies
Amit_Kumar
Community Advisor
Community Advisor

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
Community Manager
Community Manager

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
Level 5
Level 5

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
Correct answer by
Level 5
Level 5

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

View solution in original post

Amit_Kumar
Community Advisor
Community Advisor

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