[
https://jira.nuxeo.com/browse/NXP-8001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=107060#comment-107060
]
Florent Guillaume commented on NXP-8001:
----------------------------------------
So the question about the illegal
{noformat}
SELECT * FROM Loanin ORDER BY loansin_common:lenderGroupList/*/lender
{noformat}
is what exactly do you want it to return, and in what order?
If you want it to return documents, and each may have several lenders, then I
don't see how you can find a good way to define the order on the documents
based on the lenders.
You could return document ids and each lender by doing:
{noformat}
SELECT loansin_common:lenderGroupList/*1/lender, ecm:uuid FROM Loanin ORDER BY
loansin_common:lenderGroupList/*1/lender
{noformat}
but here you may get the same document id several times.
> querying for complex types and ordering is failing
> --------------------------------------------------
>
> Key: NXP-8001
> URL: https://jira.nuxeo.com/browse/NXP-8001
> Project: Nuxeo Enterprise Platform
> Issue Type: Bug
> Components: Core SQL Storage
> Affects Versions: 5.5-SNAPSHOT
> Reporter: Stéphane Lacoin
> Assignee: Florent Guillaume
> Priority: Major
> Fix For: 5.5
>
>
> Cannot execute queries with constraints on complex types and with an ordering
> clause. The generated SQL statements is not correct. The ordering field is
> not included in the output fields, but it's required (on PostgreSQL at least)
> when there's a {{DISTINCT}}.
> {code}
> SELECT * from Document where ecm:isProxy = 0 and content/name =
> 'testfile.txt' ORDER BY dc:title
> {code}
> {noformat}
> SELECT DISTINCT "HIERARCHY"."ID" AS "_C1"
> FROM "HIERARCHY"
> JOIN "HIERARCHY" "_H1" ON "HIERARCHY"."ID" = "_H1"."PARENTID"
> LEFT JOIN "CONTENT" "_F1" ON "_H1"."ID" = "_F1"."ID"
> LEFT JOIN "DUBLINCORE" "_F2" ON "HIERARCHY"."ID" = "_F2"."ID"
> WHERE "HIERARCHY"."PRIMARYTYPE" IN ('Document', '...')
> AND "_F1"."NAME" = 'testfile.txt'
> AND "_H1"."NAME" = 'content'
> ORDER BY "_F2"."TITLE"
> {noformat}
> {noformat}
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
> {noformat}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
