0

I've got a request about a search, using wildcard.

I created the following query

SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/ldap/2]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
Contains (p.memberOf, 'SUG-pilot-anna')

and it works as expected. The task of this query is to look for users, who are member of a particular ldap group.

Because there is a lot of work to search for every single group, like 

SUG-pilot-anna
SUG-pilot-berta
SUG-pilot-ceta

, it would be easier and more efficient to use the wildcard. According serveral documantations, the LIKE operator is supported by SQL2.

My question is: how does looks the query with wildcard?

Thanks for your help/idears.

Reporter
  • 3,897
  • 5
  • 33
  • 47

1 Answers1

1

You can use the % wildcard and query for your requirements.

SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/ldap/2]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
p.memberOf LIKE 'SUG-pilot-%'

This would return the list of users who belong to any group name starting with SUG-pilot-. '%' matches zero or more characters and '_' matches exactly one character.

The exact query which was working in my instance was

SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
p.memberOf LIKE '%de%'
rakhi4110
  • 9,253
  • 2
  • 30
  • 49
  • Thanks for your answer. I tested it and it generated an empty resultset compare to the first query. – Reporter Oct 23 '14 at 07:37
  • Am not sure about the ldap profile which you have nor the memberOf property that you are checking for. I tried executing that above query for existing properties and it works. Both contains and like gives me the same result. By the way which version of CQ5 are you using @reporter? – rakhi4110 Oct 23 '14 at 11:15
  • I am using CQ5.4 and the property 'memberOf' does exists in the profile node from a stored ldap user. As I wrote my query with 'Contains()' does work. – Reporter Oct 23 '14 at 11:28
  • Maybe `LIKE` does not work, because the values are stored as type of `String[]`. – Reporter Oct 23 '14 at 11:35
  • i checked LIKE for type String[] and it still returns me results. Not sure why it doesn't work for your query. Can you try '%SUG%' and see if any results are available? – rakhi4110 Oct 23 '14 at 11:54
  • When I added a percent sign at first position it did works. Thanks for help. – Reporter Oct 24 '14 at 08:44