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
