FAQ
I found some strange behaviour trying to use Distinct for the first time.
I have the following mapping
User is related with User via a mapping table named Contact, the PK is
an id as the User can contact the same target several times.

I wanted to get all distinct Users contacted by A, so as I wasn't really
sure if I needed distinct or not I first enabled it and the SQL
generated makes no sense.
My code:

          SelectQuery query =
                  new SelectQuery(User.class,
ExpressionFactory.matchExp(Cayenne.makePath(User.CONTACTED_BY_ARRAY_PROPERTY,
Contact.TO_CONTACT_OWNER_PROPERTY), owner));
          query.setDistinct(true);
          return performQuery(query);

The generated SQL query using a non null "owner":

SELECT DISTINCT t0.idUser, t0.location,
t0.PaymentInvoicingData_idInvoicingData, t0.password,
t0.ProtOnDomain_idProtOnDomain, t0.username, t0.isPremium,
t0.permissionsReadonly, t0.ProtonDomainLDAP_idDomainLdap,
t0.lockExternalVisibility, t0.usernameVisibility, t0.locked,
t0.Partner_idPartner, t0.CorporateServer_externalIdServer,
t0.premium_expiration_notification, t0.completeName, t0.externalIdUser,
t0.deleted, t0.isDomainAdmin, t0.accessReadonly, t0.profileReadonly,
t0.externalUsername, t0.accountType, t0.premiumExpiration
FROM user t0
      JOIN contact t1 ON (t0.idUser = t1.contactTarget)
WHERE t1.idContact = NULL


The SQL when distinct is commented:

SELECT DISTINCT t0.idUser, t0.location,
t0.PaymentInvoicingData_idInvoicingData, t0.password,
t0.ProtOnDomain_idProtOnDomain, t0.username, t0.isPremium,
t0.permissionsReadonly, t0.ProtonDomainLDAP_idDomainLdap,
t0.lockExternalVisibility, t0.usernameVisibility, t0.locked,
t0.Partner_idPartner, t0.CorporateServer_externalIdServer,
t0.premium_expiration_notification, t0.completeName, t0.externalIdUser,
t0.deleted, t0.isDomainAdmin, t0.accessReadonly, t0.profileReadonly,
t0.externalUsername, t0.accountType, t0.premiumExpiration FROM user t0
      JOIN contact t1 ON (t0.idUser = t1.contactTarget)
WHERE t1.contactOwner = ?
[bind: 1->contactOwner:201]


I am really not sure if current behaviour without distinct is ok, but at
least it works for me.

Best regards.

Ramiro Aparicio

Search Discussions

  • Aristedes Maniatis at Nov 12, 2013 at 10:15 pm

    On 13/11/2013 3:59am, Ramiro Aparicio wrote:

    I am really not sure if current behaviour without distinct is ok, but at least it works for me.
    Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.

    Ari





    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Nov 13, 2013 at 7:02 am
    Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.

    As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.

    Andrus
    On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis wrote:
    On 13/11/2013 3:59am, Ramiro Aparicio wrote:

    I am really not sure if current behaviour without distinct is ok, but at least it works for me.
    Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.

    Ari





    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Ramiro Aparicio at Nov 13, 2013 at 10:06 am
    So really there is little to no use for setDistinct in a SelectQuery.

    Anyway I was more concerned about the Where clause, I can assure that I
    am using a non null owner but even then owner is a User object so there
    is no reason why Cayenne should try to match agains a Contact and
    distinct should not change the where clause in this query.

    For reference Contact entity has 3 attributes:
       + contactOwner : relationship with User
       + contactTarget: relationship with User
       + idcontact: AI PK

    Andrus if you want I can open a Jira Issue

    Ramiro

    El 13/11/2013 8:00, Andrus Adamchik escribió:
    Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.

    As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.

    Andrus
    On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis wrote:
    On 13/11/2013 3:59am, Ramiro Aparicio wrote:

    I am really not sure if current behaviour without distinct is ok, but at least it works for me.
    Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.

    Ari





    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Nov 13, 2013 at 11:21 am

    So really there is little to no use for setDistinct in a SelectQuery.
    I think so too. Other than some messed up DB with de-facto non-unique PK, I can’t think why that would be needed.
    Andrus if you want I can open a Jira Issue
    Yes please. And I wonder if we should just deprecate ‘distinct’ as a solution.

    Andrus
    On Nov 13, 2013, at 1:05 PM, Ramiro Aparicio wrote:

    So really there is little to no use for setDistinct in a SelectQuery.

    Anyway I was more concerned about the Where clause, I can assure that I am using a non null owner but even then owner is a User object so there is no reason why Cayenne should try to match agains a Contact and distinct should not change the where clause in this query.

    For reference Contact entity has 3 attributes:
    + contactOwner : relationship with User
    + contactTarget: relationship with User
    + idcontact: AI PK

    Andrus if you want I can open a Jira Issue

    Ramiro

    El 13/11/2013 8:00, Andrus Adamchik escribió:
    Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.

    As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.

    Andrus
    On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis wrote:
    On 13/11/2013 3:59am, Ramiro Aparicio wrote:

    I am really not sure if current behaviour without distinct is ok, but at least it works for me.
    Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.

    Ari





    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Dollj at Nov 13, 2013 at 12:58 pm

    With regards to:
    I wonder if we should just deprecate ‘distinct’ as a solution.
    What about making it a no op ? With the Java doc explaining that it does
    nothing because "Cayenne forces DISTINCT in the generated SQL whether you
    specify it or not because it detects a match on to-many relationship and
    realizes that it needs to get rid of duplicates in the cartesian product."
    (Andrus)

    So really there is little to no use for setDistinct in a SelectQuery.
    I think so too. Other than some messed up DB with de-facto non-unique PK,
    I can’t think why that would be needed.
    Andrus if you want I can open a Jira Issue
    Yes please. And I wonder if we should just deprecate ‘distinct’ as a
    solution.

    Andrus
    On Nov 13, 2013, at 1:05 PM, Ramiro Aparicio wrote:

    So really there is little to no use for setDistinct in a SelectQuery.

    Anyway I was more concerned about the Where clause, I can assure that I
    am using a non null owner but even then owner is a User object so there
    is no reason why Cayenne should try to match agains a Contact and
    distinct should not change the where clause in this query.

    For reference Contact entity has 3 attributes:
    + contactOwner : relationship with User
    + contactTarget: relationship with User
    + idcontact: AI PK

    Andrus if you want I can open a Jira Issue

    Ramiro

    El 13/11/2013 8:00, Andrus Adamchik escribió:
    Cayenne forces DISTINCT in the generated SQL whether you specify it or
    not because it detects a match on to-many relationship and realizes
    that it needs to get rid of duplicates in the cartesian product. So I
    guess explicit DISTINCT shouldn’t normally be needed.

    As to why NULL is bound in the DISTINCT case, this could either be a
    yet unknown bug in the framework, or something in the app code.

    Andrus

    On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis <ari@maniatis.org>
    wrote:
    On 13/11/2013 3:59am, Ramiro Aparicio wrote:

    I am really not sure if current behaviour without distinct is ok, but
    at least it works for me.
    Cayenne returns a collection of objects that will never have
    duplicates. In fact you can choose whether the results return as a
    Set, List or Map.

    Ari

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedNov 12, '13 at 5:00p
activeNov 13, '13 at 12:58p
posts6
users4
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase