Don't want duplicate entries from query | Community
Skip to main content
Abhishekty
Abhishekty
Level 4
December 21, 2021
Solved

Don't want duplicate entries from query

  • December 21, 2021
  • 2 replies
  • 1279 views

Hi, 

 

I have an existing query and I have updated it with one filter but now I am getting duplicate results. I don't want duplicate entries.

Query: 

 

 SELECT * FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/myProject/en/devices])) and s.derivedInventoryStatus = 'Back Order' and s.availabilityStatus = 'Sellable'

 

for unique results, do I need to update any properties for the below nodes ?

 

1. /oak:index/lucene/indexRules/nt:base/properties/derivedInventoryStatus

2. /oak:index/lucene/indexRules/nt:base/properties/availabilityStatus

 

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 Vijayalakshmi_S

Thanks for your inputs @abhishekty 

We can create Property Index which allows for unique constraint on respective property. In this case, we can create property index for the property named ensembleId.

With this in place, it won't let you copy the node at first place/to have a node with duplicate ensembleId (Unique constraint violation exception would be thrown)

OOTB property index definition with unique constraint support - 

  • /oak:index/uuid
  • /oak:index/authorizableId

You can refer the same and create one for ensembleId (declaringNodeTypes - nt:unstructured, propertyNames - ensembleId)

2 replies

arunpatidar
Community Advisor
arunpatidarCommunity Advisor
Community Advisor
December 21, 2021

Unfortunately, there are no aggregation functions in JCR SQL|XPath|JCR-SQL2. The only thing I can suggest you, is to iterate over these nodes and do all calculations or put path in SET in your code

Arun Patidar
    Vijayalakshmi_S
    Vijayalakshmi_S
    Level 10
    December 22, 2021

    Hi @abhishekty

    Could you please let know

    • your existing query (original query)
    • additional filter that you are referring to (which results in duplicate results)
    • AEM instance version

     

      Abhishekty
      AbhishektyAuthor
      Level 4
      December 22, 2021

      @vijayalakshmi_s , Thanks for reply.

       

      Please my inline comments : 

       

      • your existing query (original query) : SELECT * FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/myProject/en/devices])) and s.availabilityStatus = 'Sellable'
      • additional filter that you are referring to (which results in duplicate results): s.derivedInventoryStatus = 'Back Order'    One thing I have observed is if I have copy-pasted the same device (on etc/commerce/products/myProject/in/devices) then also I am getting both node data with the original query. Can we isolate the result with some unique key (as currently, it has
        ensembleId as unique key)
        •                                                                I am thinking if in AEM also we can apply DISTINCT for unique results similar to SQL. 
      • AEM instance version Adobe Experience Manager 6.5.6.0
        Vijayalakshmi_S
        Vijayalakshmi_SAccepted solution
        Level 10
        December 23, 2021

        Thanks for your inputs @abhishekty 

        We can create Property Index which allows for unique constraint on respective property. In this case, we can create property index for the property named ensembleId.

        With this in place, it won't let you copy the node at first place/to have a node with duplicate ensembleId (Unique constraint violation exception would be thrown)

        OOTB property index definition with unique constraint support - 

        • /oak:index/uuid
        • /oak:index/authorizableId

        You can refer the same and create one for ensembleId (declaringNodeTypes - nt:unstructured, propertyNames - ensembleId)

          Terms & ConditionsAccessibility statement

          Loading footer...