topic Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query? in Nuxeo Forum

SELECT DISTINCT, ORDER BY expressions in Studio content view query?

karl_harris_ — Thu, 29 Dec 2011 17:37:36 GMT

I'm using the following query for a content view using Studio:

ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND zdoctype:ztype ILIKE 'zaxis_customer' AND ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%'

The Contact field is a complex list within which one of the fields is ContactEmail and I'm trying to search for the ZC1236 pattern and list all the documents which contain that email pattern.

When executed I get the following error:

SELECT DISTINCT, ORDER BY expressions must appear in select list.

It seems I do not have control, in Studio, with where the SELECT DISTINCT AND ORDER BY expression appear in the query.

Can this type of query work in studio content view?

The following query works in nuxeo shell:

Select * FROM Customer Where ( ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%')

Ultimately I'd like to take an email address and query the customer documents for a match then relate that email to the customer document.

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

Wojciech_Sulejm — Thu, 29 Dec 2011 23:04:21 GMT

Your error message seems to be coming from the database (PostgreSQL?), not from NXQL. In that light, the results of your tests with the Nuxeo Shell seem confusing to me. It might sound like a naive question but are you performing both tests against the same running Nuxeo instance?

Also, reading from your description I am not sure you need this syntax for your specific scenario:

ZCUSTII:Contact/*/ContactEmail

Could you try to replace the above fragment with the following instead and let us know about the results? :

ZCUSTII:Contact/ContactEmail

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

karl_harris_ — Fri, 30 Dec 2011 15:55:51 GMT

Yes, I am using PostgreSQL, single instance.

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

Wojciech_Sulejm — Fri, 30 Dec 2011 16:38:29 GMT

Could you try matching both queries in both systems?

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

karl_harris_ — Fri, 30 Dec 2011 18:16:09 GMT

After I removed the suggested fragment, in Studio, I got the following error

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

Florent_Guillau — Mon, 02 Jan 2012 15:21:54 GMT

Unrelated note

Re: SELECT DISTINCT, ORDER BY expressions in Studio content view query?

Florent_Guillau — Mon, 02 Jan 2012 15:30:31 GMT

The message comes from Nuxeo, see here. Indeed it seems that the content view generates a query with either an explicit DISTINCT or explicit columns but not the one from the ORDER BY. Could you include the XML for your content view? Or debug to see what NXQL query is sent? Ultimately it's likely a bug, so opening a new NXP and copy the URL into an answer to this question would be a good move.