topic Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT in Nuxeo Forum

LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Rafael_Bueno — Tue, 13 May 2014 02:37:30 GMT

Hi!

Can someone help me ? I'm trying to make a LEFT JOIN QUERY, but the query return duplicate result. Its possible make a DISTINCT or GROUPBY ? Why the query return the JOIN values to ? The result dont need to show me only F.cmis:objectId as ID, F.cmis:name as name this columns ?

SELECT F.cmis:objectId as ID, F.cmis:name as name  FROM arizona:file:business F
LEFT JOIN arizona:relationship:copyrights RFC ON RFC.cmis:sourceId = F.cmis:objectId 
LEFT JOIN arizona:copyrights C ON C.cmis:objectId = RFC.cmis:targetId 
LEFT JOIN arizona:copyrights:permission P ON P.arizonaCopyrightsPermission:copyrightsId = C.cmis:objectId

Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Florent_Guillau — Tue, 13 May 2014 15:16:49 GMT

The CMIS Query Language does not support DISTINCT or GROUP BY. See the spec § 2.1.14.2.1 BNF Grammar.

Nuxeo returns the object id and type for each joined document because this has proven to be very useful, and is used internally. Why is it a problem? The tool that is going to consume that data will only keep what it needs anyway.

Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Rafael_Bueno — Tue, 13 May 2014 15:46:39 GMT

Florent, do you know how resolve that using some other strategy ? Do you know something about the other questions ?

Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Florent_Guillau — Tue, 13 May 2014 16:35:43 GMT

Answer edited.

Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Rafael_Bueno — Tue, 13 May 2014 20:06:27 GMT

I cant create the max itens to the paginate

Re: LEFT JOIN QUERY RESULT GROUP BY OR DISTINCT

Florent_Guillau — Thu, 15 May 2014 12:41:03 GMT

De-duplicate in your client.