Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

AEM 6.3 | Predicate search | issue with orderby

Avatar

Level 2

Hello Experts,

We are in process to upgrade our site from 6.2 to 6.3. While testing search feature on 6.3 we have noticed that resultset sorting stopped working. Though the same code/query is working on 6.2.

We are using Predicate query builder to create our query.

private static List<Predicate> getSortOrderPredicateGroup(final List<SortCriteria> sortCriterias) {

        final List<Predicate> sortPredicates = new ArrayList<Predicate>();

        int count = 0;

        for (final SortCriteria sortCriteria : sortCriterias) {

            count++;

            final Predicate sortPredicate = new Predicate(SearchConstants.PREDICATE_PREFIX_SORT_ORDER + count,

                    SearchConstants.PREDICATE_TYPE_ORDERBY);

            sortPredicate.set(SearchConstants.PREDICATE_TYPE_ORDERBY, sortCriteria.getProperty());

            sortPredicate.set(SearchConstants.PREDICATE_PARAM_SORT, sortCriteria.getOrder().getValue());

            sortPredicates.add(sortPredicate);

        }

        return sortPredicates;

    }

and then we add this sortOrder predicate in query

query.getPredicates().addAll(getSortOrderPredicateGroup(searchParameters.getSortOrder()));

The predicate query generated is

FilterFlagGroup.Property_1=jcr%3acontent%2fhideInSitemap

FilterFlagGroup.Property_1.operation=contains

FilterFlagGroup.Property_1.value=true

FilterFlagGroup.Property_2=..%2f..%2fjcr%3acontent%2fjcr%3aprimaryType

FilterFlagGroup.Property_2.operation=contains

FilterFlagGroup.Property_2.value=dam%3aAssetContent

FilterFlagGroup.p.not=true

FilterFlagGroup.p.or=true

KeywordGroup.FullText_Keyword_1=%22insurance%22

KeywordGroup.FullText_Keyword_1.relPath=.

KeywordGroup.p.or=true

PathGroup.Path_1=%2fcontent%2fweretail%2fen

PathGroup.Path_2=%2fcontent%2fdam%2fweretail%2fdocuments

PathGroup.p.or=true

SortOrder_1=%40jcr%3acontent%2fjcr%3alastModified

SortOrder_1.sort=desc

TypeGroup.Type_1=cq%3aPage

TypeGroup.Type_2=dam%3aAsset

TypeGroup.p.or=true

p.limit=10

p.offset=0

The SQL query created using above predicate query (in 6.3) is:

select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/weretail/en') union select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/dam/weretail/documents')

order by [jcr:content/jcr:lastModified] desc

union(*)select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/weretail/en')

order by [jcr:content/jcr:lastModified] desc

union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/dam/weretail/documents')

order by [jcr:content/jcr:lastModified] desc

As you notice, the generated SQL query has two issues:

  1. Orderby is added 3 times instead of one time for entire query (line 2, 4, 6)
  2. Line 3, union is followed by unwanted (*)

I am not able to figure out if we need to change our predicate to correct this behavior or its a defect. Any help is really appreciated.

Regards

Mohit Bansal

0 Replies