Add different limit for each group in query builder

[Aravind2407]

For the below query, how we can add the limit for each group. (group.1, group.2, group.3)

path=/content/dam/documents
type=dam:Asset
1_property = jcr:content/metadata/dc:format
1_property.value=application/pdf
group.p.or=true
group.1_group.3_property=documentType
group.1_group.3_property.value=type1
group.2_group.3_property=documentType
group.2_group.3_property.value=type2
group.3_group.3_property=documentType
group.3_group.3_property.value=type3
orderby=@publishDate
orderby.sort=desc

Thanks in advance,

[Kamal_Kishor]

@Aravind2407 : Can you please elaborate on what do you mean by putting a limit? Also, can you please explain what you are trying to achieve with your query so that it gives some context to your question?

In general you can refer some of the help text for queries at : https://github.com/paulrohrbeck/aem-links/blob/master/querybuilder_cheatsheet.md

https://github.com/yurishchev/aem-links/blob/master/querybuilder-cheatsheet.md

In context of groups there are some options to group them in condition of AND/OR (default is AND). Limit is something that you specify in your query to mention how many results you need as part of query response i.e "p.limit=20" it gives 20 results, by default it returns 10.

thanks.

[Aravind2407]

Hi @Kamal_Kishor , Thank you for your reply.

For my case i need to get 1 document for each type.

type1-1 document

type2-1 document

type3-1 document

so i need to set the limit - 1 for each group. If i put limit 3 at the end it will give top 3 (that may be in same type)

[Kamal_Kishor]

@Aravind2407 : I do not think it is possible to do so using just 1 query. Other options could be to have separate query for each group and then combine the results of all to get 3 results for each document type.

thanks.

[Aravind2407]

okay, but that will make increase the number of query hits.

[Kamal_Kishor]

@Aravind2407 : Yes it will increase the number of query on your instance with that approach.

Alternatively, you can try this:-

1. Get all the document types (i.e type1,type2,type3) using a query.
2. In your backend logic, group the result set based on type and then extract only whatever you need for each type.

For grouping you can refer this (or just search for ways to group on object property)- https://javarevisited.blogspot.com/2015/07/how-to-do-group-by-in-java-8.html#axzz8QZrWHQjd

Here is another response which says you can't achieve the limit on each group in a single query.
https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/aem6-3-group-results-in-qu...

Please try and let me know if this helps.
thanks.

[Raja_Reddy]

Hi @Aravind2407 
Please try below query

To add a limit for each group in the query, you can use the group.limit parameter. This parameter specifies the maximum number of results to return for each group

path=/content/dam/documents
type=dam:Asset
1_property=jcr:content/metadata/dc:format
1_property.value=application/pdf
group.p.or=true
group.1_group.3_property=documentType
group.1_group.3_property.value=type1
group.1_group.limit=10
group.2_group.3_property=documentType
group.2_group.3_property.value=type2
group.2_group.limit=10
group.3_group.3_property=documentType
group.3_group.3_property.value=type3
group.3_group.limit=10
orderby=@publishDate
orderby.sort=desc

In this example, we have added the group.1_group.limit, group.2_group.limit, and group.3_group.limit parameters to limit the number of results for each group to 10. You can adjust the limit value as per your requirement.

Thanks.

[Aravind2407]

Hi @Raja_Reddy ,

Thanks for your reply, I have tried that group limit, still getting same result.

[Kamal_Kishor]

@Raja_Reddy: Have you seen the limit at group level predicate working properly? I have tried it as well and seems to not work.

[PAGIDALAGURUKRISHNA]

Hi @Aravind2407 

To achieve the requirement of retrieving only one document for each type using the AEM Query Builder, you can't directly set a limit per group within a single query since the Query Builder does not support this functionality out of the box. However, you can still accomplish your goal by running separate queries for each type, each with a limit of 1. This ensures that you get one document per type.

Here's how you would set up separate queries:

// Query for type1
path=/content/dam/documents
type=dam:Asset
1_property=jcr:content/metadata/dc:format
1_property.value=application/pdf
2_property=@jcr:content/metadata/documentType
2_property.value=type1
p.limit=1
orderby=@publishDate
orderby.sort=desc

// Query for type2
path=/content/dam/documents
type=dam:Asset
1_property=jcr:content/metadata/dc:format
1_property.value=application/pdf
2_property=@jcr:content/metadata/documentType
2_property.value=type2
p.limit=1
orderby=@publishDate
orderby.sort=desc

// Query for type3
path=/content/dam/documents
type=dam:Asset
1_property=jcr:content/metadata/dc:format
1_property.value=application/pdf
2_property=@jcr:content/metadata/documentType
2_property.value=type3
p.limit=1
orderby=@publishDate
orderby.sort=desc

In these queries, p.limit=1 is set for each separate query to ensure that only one result is returned for each document type. The orderby=@publishDate and orderby.sort=desc parameters will ensure that the most recently published document of each type is returned.

Running these queries separately and combining the results will give you one document for each type as required. This approach is the most straightforward way to bypass the limitation of the AEM Query Builder in not being able to specify per-group limits within a single query.

If you are implementing this within a Java service or an OSGi component, you can programmatically execute these queries one after the other and aggregate the results. If your use case requires this to be done frequently or at scale, you might consider implementing a custom service that abstracts this logic away, so you don't have to manually run and manage multiple queries each time.

For more detailed information, you can refer to the official documentation on the Query Builder API and Predicate Reference:

- Query Builder API: https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/implementing/develo...
- Query Builder Predicate Reference: https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/implementing/develo...

[Aravind2407]

Hi @PAGIDALAGURUKRISHNA ,

Thanks for your reply, For this case, we need to hit the multiple query, its not a recommended way. (if more type authored then i need to hit more queries)

[kautuk_sahni]

@Aravind2407 Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.

[Aravind2407]

Hi @kautuk_sahni ,

I'm trying to find the solution. As of now there is no accepted solution from the users.

If i got any solution i will post here.

[PAGIDALAGURUKRISHNA]

Hey Aravind, 

Understood, if executing multiple queries is not a recommended approach due to potential performance issues or if the number of document types may increase, the situation becomes more complex due to the limitations of the AEM Query Builder API.

Since the Query Builder does not natively support setting a limit for each group in a single query, one potential solution is to use the built-in capabilities of AEM's search functionality to retrieve all documents and then filter and limit the results programmatically. However, this also poses performance concerns because it involves fetching more data than needed from the repository.

Another alternative approach is to implement a custom query post-processing logic that interacts with the search results to enforce the limit per type after the results have been retrieved. This logic could be part of a custom AEM service or servlet. Here's a conceptual overview of how this might work:

1. Perform a single query to retrieve all documents of interest, without specifying a limit.
2. Within your custom logic (Java/Sling/AEM service), categorize the results into buckets based on the document type.
3. From each bucket, select the top result based on the sorting order (e.g., orderby=@publishDate and orderby.sort=desc).
4. Aggregate the top results from each bucket to form the final result set.

This approach minimizes the number of queries executed against the repository and allows for more flexibility if the number of document types changes. However, it does require fetching all documents of the specified types, which could be a large number, and then post-processing them, which could impact performance depending on the size of the result set.

[Aravind2407]

Hi @PAGIDALAGURUKRISHNA ,

I have that solution and wrote that code as well, i'm trying to avoid the execution time and the performance.

Thanks for your reply.

I'm trying other ways also using SQL2.