SELECT DISTINCT, ORDER BY expressions in Studio content view query?
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.
 |
karl_harris
Member (3.4K) 3 6 6 |
12/29/2011 ( History) |
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.
|
Florent Guillaume
Member (36K) 6 8 8 |
01/02/2012 ( History) |
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
 |
Wojciech Sulejman
Member (3K) 3 5 4 |
12/29/2011 ( History) |
When I execute the query:
ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND zdoctype:ztype ILIKE 'zaxis_customer' AND ZCUSTII:Contact/ContactEmail ILIKE '%ZC1236%'
in Nuxeo I get the following error
Failed to execute query: No such property: ZCUSTII:Contact/ContactEmail
Executing the query:
query "Select * FROM Document Where ( ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND zdoctype:ztype ILIKE 'zaxis_customer' AND ZCUSTII:Contact/ContactEmail ILIKE '%ZC1236%')"
in the Nuxeo shell on the same instance of Nuxeo, I only have a single instance running, I get the following error:
org.nuxeo.ecm.automation.client.RemoteException: Failed to execute operation: Document.Query
Executing the query:
query "Select * FROM Document Where ( ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND zdoctype:ztype ILIKE 'zaxis_customer' AND ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%')"
in Nuxeo shell gives me a listing of matching documents.
I will re-read the complex type query documentation to be sure I'm not missing anything.
Thanks for your help.
Karl
In shell try this:
SELECT * FROM Document WHERE ( ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND zdoctype:ztype ILIKE 'zaxis_customer' AND ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%' )"
… and in studio just this short clause:
ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%'
(that is without the following condition: AND zdoctype:ztype ILIKE 'zaxis_customer' to make sure they both are the same)
Failed to execute query: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
FWIW removing the ZCUSTII:Contact/*/ContactEmail ILIKE '%ZC1236%' fragment allows the query to complete successfully complete, of course without the email "filter" on the Contact complex type.