Don't want duplicate entries from query | Community
Skip to main content
Abhishekty
Abhishekty
Level 4
Dec 21, 2021 8:36 AM UTC
Solved

Don't want duplicate entries from query

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.
Vijayalakshmi_S
Vijayalakshmi_SAccepted solution
Level 10
Dec 23, 2021 4:11 PM UTC

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)

View reply

4 replies

Vijayalakshmi_S
Vijayalakshmi_S
Level 10
Dec 22, 2021 4:26 PM UTC

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

 

Translate
    Abhishekty
    AbhishektyAuthor
    Level 4
    Dec 22, 2021 11:42 PM UTC

    @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
    Translate
      Vijayalakshmi_S
      Vijayalakshmi_SAccepted solution
      Level 10
      Dec 23, 2021 4:11 PM UTC

      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)

      Translate
        arunpatidar
        arunpatidar
        New Participant
        Dec 21, 2021 5:14 PM UTC

        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

        Translate
        Arun Patidar
          Terms & ConditionsAccessibility statement

          Loading footer...