[xwiki-users] XWQL : order by count ?
Hi, I'm trying to use XWQL query language to select count of authors and would like to order this by descending order of this count. I ended up having this work fine : "select count(myobject.author), myobject.author from Document as doc, doc.object(Space.MyClass) as myobject group by myobject.author" But adding " order by count(myobject.author) desc" leads to an error for translating the query ... Adding "AS" in the select expression does not seem to work either. Is this possible in XWQL and how ? Thanks, Jeremie -- View this message in context: http://xwiki.475771.n2.nabble.com/XWQL-order-by-count-tp6904819p6904819.html Sent from the XWiki- Users mailing list archive at Nabble.com.
Hello, Seems I'm back to this problem (XWiki 3.5), and I did not find a solution still ... For example this XWQL request : select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by count(topic.author) desc ... will throw this exception when executed : Caused by: org.xwiki.query.jpql.parser.ParserException: [1,165] expecting: id at org.xwiki.query.jpql.parser.Parser.parse(Parser.java:1247) at org.xwiki.query.jpql.internal.JPQLParser.parse(JPQLParser.java:46) at org.xwiki.query.xwql.internal.hql.XWQLtoHQLTranslator.translate(XWQLtoHQLTranslator.java:55) at org.xwiki.query.xwql.internal.XWQLQueryExecutor.execute(XWQLQueryExecutor.java:78) ... 123 more Column 165 refers to the count in the order by clause. This does not work better : select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by 2 desc It does not work with a MySQL nor HSQLDB default db. If I remove the order by clause, the query works correctly. I think it's strange because for me ORDER BY COUNT(<column>) is valid in JPQL ? As long as the expression is also present in the SELECT clause. Thanks, Jeremie 2011/10/18 jerem <jeremie.bousquet@gmail.com>
Hi,
I'm trying to use XWQL query language to select count of authors and would like to order this by descending order of this count.
I ended up having this work fine :
"select count(myobject.author), myobject.author from Document as doc, doc.object(Space.MyClass) as myobject group by myobject.author"
But adding " order by count(myobject.author) desc" leads to an error for translating the query ... Adding "AS" in the select expression does not seem to work either.
Is this possible in XWQL and how ?
Thanks, Jeremie
-- View this message in context: http://xwiki.475771.n2.nabble.com/XWQL-order-by-count-tp6904819p6904819.html Sent from the XWiki- Users mailing list archive at Nabble.com. _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Hello, Seems every year I hit this problem (ORDER BY COUNT in XWQL causes a parser exception), so this time I decided to create an issue ... :) : http://jira.xwiki.org/browse/XWIKI-10189 BR, Jeremie 2012-05-24 15:13 GMT+02:00 Jeremie BOUSQUET <jeremie.bousquet@gmail.com>:
Hello,
Seems I'm back to this problem (XWiki 3.5), and I did not find a solution still ...
For example this XWQL request :
select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by count(topic.author) desc
... will throw this exception when executed :
Caused by: org.xwiki.query.jpql.parser.ParserException: [1,165] expecting: id at org.xwiki.query.jpql.parser.Parser.parse(Parser.java:1247) at org.xwiki.query.jpql.internal.JPQLParser.parse(JPQLParser.java:46) at org.xwiki.query.xwql.internal.hql.XWQLtoHQLTranslator.translate(XWQLtoHQLTranslator.java:55) at org.xwiki.query.xwql.internal.XWQLQueryExecutor.execute(XWQLQueryExecutor.java:78) ... 123 more
Column 165 refers to the count in the order by clause.
This does not work better :
select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by 2 desc
It does not work with a MySQL nor HSQLDB default db.
If I remove the order by clause, the query works correctly.
I think it's strange because for me ORDER BY COUNT(<column>) is valid in JPQL ? As long as the expression is also present in the SELECT clause.
Thanks, Jeremie
2011/10/18 jerem <jeremie.bousquet@gmail.com>
Hi,
I'm trying to use XWQL query language to select count of authors and
would
like to order this by descending order of this count.
I ended up having this work fine :
"select count(myobject.author), myobject.author from Document as doc, doc.object(Space.MyClass) as myobject group by myobject.author"
But adding " order by count(myobject.author) desc" leads to an error for translating the query ... Adding "AS" in the select expression does not seem to work either.
Is this possible in XWQL and how ?
Thanks, Jeremie
-- View this message in context: http://xwiki.475771.n2.nabble.com/XWQL-order-by-count-tp6904819p6904819.html Sent from the XWiki- Users mailing list archive at Nabble.com. _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
On 27 Mar 2014 at 21:01:49, Jeremie BOUSQUET (jeremie.bousquet@gmail.com(mailto:jeremie.bousquet@gmail.com)) wrote:
Hello,
Seems every year I hit this problem (ORDER BY COUNT in XWQL causes a parser exception), so this time I decided to create an issue ... :) :
cool :) <kidding> Maybe you’ll be able to provide a patch next year! ;) </kidding> Thanks -Vincent
BR, Jeremie
2012-05-24 15:13 GMT+02:00 Jeremie BOUSQUET :
Hello,
Seems I'm back to this problem (XWiki 3.5), and I did not find a solution still ...
For example this XWQL request :
select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by count(topic.author) desc
... will throw this exception when executed :
Caused by: org.xwiki.query.jpql.parser.ParserException: [1,165] expecting: id at org.xwiki.query.jpql.parser.Parser.parse(Parser.java:1247) at org.xwiki.query.jpql.internal.JPQLParser.parse(JPQLParser.java:46) at org.xwiki.query.xwql.internal.hql.XWQLtoHQLTranslator.translate(XWQLtoHQLTranslator.java:55) at org.xwiki.query.xwql.internal.XWQLQueryExecutor.execute(XWQLQueryExecutor.java:78) ... 123 more
Column 165 refers to the count in the order by clause.
This does not work better :
select topic.author, count(topic.author) from Document doc, doc.object(MailArchiveCode.MailTopicClass) as topic where doc.space<>'MailArchiveCode' group by topic.author order by 2 desc
It does not work with a MySQL nor HSQLDB default db.
If I remove the order by clause, the query works correctly.
I think it's strange because for me ORDER BY COUNT() is valid in JPQL ? As long as the expression is also present in the SELECT clause.
Thanks, Jeremie
2011/10/18 jerem
Hi,
I'm trying to use XWQL query language to select count of authors and
would
like to order this by descending order of this count.
I ended up having this work fine :
"select count(myobject.author), myobject.author from Document as doc, doc.object(Space.MyClass) as myobject group by myobject.author"
But adding " order by count(myobject.author) desc" leads to an error for translating the query ... Adding "AS" in the select expression does not seem to work either.
Is this possible in XWQL and how ?
Thanks, Jeremie
-- View this message in context: http://xwiki.475771.n2.nabble.com/XWQL-order-by-count-tp6904819p6904819.html Sent from the XWiki- Users mailing list archive at Nabble.com. _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
participants (3)
-
jerem -
Jeremie BOUSQUET
-
vincent＠massol.net