Cq query combining dates and "does not contain" clause | Community
Skip to main content
C
chetanvajre2014
Level 4
February 8, 2016
Solved

Cq query combining dates and "does not contain" clause

  • February 8, 2016
  • 6 replies
  • 3109 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by chetanvajre2014

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

6 replies

smacdonald2008
smacdonald2008
Level 10
February 8, 2016

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
Amit_Kumar
Level 10
February 8, 2016

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
kautuk_sahniCommunity Manager
Community Manager
February 9, 2016

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

Kautuk Sahni
C
chetanvajre2014Author
Level 4
February 9, 2016

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

C
chetanvajre2014AuthorAccepted solution
Level 4
February 9, 2016

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
Amit_Kumar
Level 10
February 9, 2016

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

Terms & ConditionsAccessibility statement

Loading footer...