Expand my Community achievements bar.

SOLVED

How to leverage JCR SQL2 Query with LIKE Operator

Avatar

Level 4

Hi, 

We have a situation where we are currently querying to find an Image with the below pattern
abcd_108489356_en.jpg and it is giving the results.


SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/some-company/creative-assets/automobiles/offers]) AND a.[jcr:path] LIKE '%/108489356_en.jpg' ORDER BY a.[jcr:created]

Sometimes images in the DAM would be something like 108489356_enfr.jpg

How can we leverage the above Query to find 108489356_enfr.jpg if 108489356_en.jpg doesn't exist? 

Thanks,

@

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

@mrudul what about the below query?

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/some-company/creative-assets/automobiles/offers]) AND a.[jcr:path] LIKE 'abcd_108489356%' ORDER BY a.[jcr:created]

View solution in original post

7 Replies

Avatar

Correct answer by
Community Advisor

@mrudul what about the below query?

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/some-company/creative-assets/automobiles/offers]) AND a.[jcr:path] LIKE 'abcd_108489356%' ORDER BY a.[jcr:created]

Avatar

Level 4

@Nitin_laad @DEBAL_DAS 

Thanks for this.
Of late there is a small change in requirement. 
In DAM we would have something like this 

 

scenario 1: 1000590_en.jpg AND 1000590_fr.jpg
scenario 2: 1000590_enfr.jpg - (If this exist there will not be above, scenario 1)

And in the request, we always have either 1000590_en.jpg or 1000590_fr.jpg
How can we achieve this with a single Query, not successive Queries

Avatar

Community Advisor

@mrudulThis should be a two-step process, in my opinion. The first step is to retrieve the relevant data from JCR, and the second step is to apply JAVA logic to filter the record as per your requirement. 

Avatar

Employee Advisor

That will work, but it will slow down the query quite a bit because it is no longer a substring operation, but you need are effectively doing a fulltext match on the path. Although given that the paths are not huge (in terms of text) it should be acceptable.

But be aware, that it might yield in unexpected results, because it will match basically everything containing that string. You need to post-process the results.

Avatar

Employee Advisor

Another approach you could use also, like operator with NAME() function as shown below -

 

DEBAL_DAS_0-1648106998767.png

 

DEBAL_DAS_1-1648107029040.png