[xwiki-users] Unexpected 'Order By' Results
Hello, I am having some issues with the "order by" clause in HQL - the following query: $xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc") is returning the expected set of documents, but the title order is erratic. The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have 'A', 'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these spans, no items appear out of order, but the 'span' separation is not expected or desired. Is there a common issue that I could be overlooking? Thank you, Chris -- View this message in context: http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5702... Sent from the XWiki- Users mailing list archive at Nabble.com.
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the following query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")
is returning the expected set of documents, but the title order is erratic. The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have 'A', 'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these spans, no items appear out of order, but the 'span' separation is not expected or desired. Is there a common issue that I could be overlooking?
There are two types of titles. Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content. Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries. I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title. -- Sergiu Dumitriu http://purl.org/net/sergiu/
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the following query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly braces to wrap doc.parent? Thanks!
is returning the expected set of documents, but the title order is erratic. The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have 'A', 'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these spans, no items appear out of order, but the 'span' separation is not expected or desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
Hello, Sorry for the delay in reply. Sergiu, that was the case. Thank you very much. :) Ricardo, the query is to include a list of all child documents in order. I included the curly braces in the query simply because I find it easier to read. Thank you, Chris On Thu, Nov 4, 2010 at 3:36 AM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [email protected]<ml-node%[email protected]>
wrote:
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the following query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}'
order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly braces to wrap doc.parent?
Thanks!
is returning the expected set of documents, but the title order is erratic. The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have 'A', 'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these spans, no items appear out of order, but the 'span' separation is not expected or desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5704373&i=0> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @ http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5704... To unsubscribe from Unexpected 'Order By' Results, click here<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==>.
-- View this message in context: http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5708... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi! Chris Wagner wrote:
Hello,
Sorry for the delay in reply.
Sergiu, that was the case. Thank you very much. :)
Ricardo, the query is to include a list of all child documents in order. I included the curly braces in the query simply because I find it easier to read.
Thanks, Chris. Far clear the reason to use curly braces, but I am not able to understand the query. If I understand well, it lists all documents with a given patent. Thus... {{velocity}} #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")) #foreach($item in $results) #set ($itemdoc = $xwiki.getDocument($item)) [$item>$item], #end {{/velocity}} must list all documents having ${doc.parent} as parent. So, included in a page ICT.WebHome, must list all child documents having ICT.WebHome as parent, mustn't it? Well, I get nothing by including this snippet in such ICT.WebHome page. And there are 50 documents that have this age as parent page! Please, where am I wrong? One more things. Please, how is it possible to compare doc.id and $doc.parent? As far as I understand, doc.id maps to XWD_ID in xwikidoc table (it is a bigint(20) field). And $doc.parent invokes getParent() and gets a string in the form Space.Document. Thus, how is it possible to compare both values? Thanks for your help!
Thank you, Chris
On Thu, Nov 4, 2010 at 3:36 AM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [email protected]<ml-node%[email protected]>
wrote:
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the
following
query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}'
order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly braces to wrap doc.parent?
Thanks!
is returning the expected set of documents, but the title order is
erratic.
The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have
'A',
'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these
spans,
no items appear out of order, but the 'span' separation is not expected
or
desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5704373&i=0> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @ http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5704... To unsubscribe from Unexpected 'Order By' Results, click here<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==>.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
Hello, doc.id should be doc.fullName in the query, sorry. The real Velocity snippet is a little bit longer than that, and while trying to trim it down for clarity it looks like I broke it. This is probably why nothing was being listed when you tried it. - Chris On Fri, Nov 5, 2010 at 6:54 PM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [email protected]<ml-node%[email protected]>
wrote:
Hi!
Chris Wagner wrote:
Hello,
Sorry for the delay in reply.
Sergiu, that was the case. Thank you very much. :)
Ricardo, the query is to include a list of all child documents in order. I included the curly braces in the query simply because I find it easier to
read.
Thanks, Chris. Far clear the reason to use curly braces, but I am not able to understand the query. If I understand well, it lists all documents with a given patent. Thus...
{{velocity}} #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")) #foreach($item in $results) #set ($itemdoc = $xwiki.getDocument($item)) [$item>$item], #end {{/velocity}}
must list all documents having ${doc.parent} as parent. So, included in a page ICT.WebHome, must list all child documents having ICT.WebHome as parent, mustn't it?
Well, I get nothing by including this snippet in such ICT.WebHome page. And there are 50 documents that have this age as parent page!
Please, where am I wrong?
One more things. Please, how is it possible to compare doc.id and $doc.parent? As far as I understand, doc.id maps to XWD_ID in xwikidoc table (it is a bigint(20) field). And $doc.parent invokes getParent() and gets a string in the form Space.Document. Thus, how is it possible to compare both values?
Thanks for your help!
Thank you, Chris
On Thu, Nov 4, 2010 at 3:36 AM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [hidden email] <http://user/SendEmail.jtp?type=node&node=5710870&i=0><[hidden email] <http://user/SendEmail.jtp?type=node&node=5710870&i=1>>
wrote:
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the
following
query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}'
order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly
braces to wrap doc.parent?
Thanks!
is returning the expected set of documents, but the title order is
erratic.
The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have
'A',
'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these
spans,
no items appear out of order, but the 'span' separation is not expected
or
desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from
the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5704373&i=0> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @
To unsubscribe from Unexpected 'Order By' Results, click here< http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==&by-user=t>>.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5710870&i=2> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @ http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5710... To unsubscribe from Unexpected 'Order By' Results, click here<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==>.
-- View this message in context: http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5711... Sent from the XWiki- Users mailing list archive at Nabble.com.
On 11/05/2010 06:53 PM, [Ricardo Rodriguez] eBioTIC. wrote:
Hi!
Chris Wagner wrote:
Hello,
Sorry for the delay in reply.
Sergiu, that was the case. Thank you very much. :)
Ricardo, the query is to include a list of all child documents in order. I included the curly braces in the query simply because I find it easier to read.
Thanks, Chris. Far clear the reason to use curly braces, but I am not able to understand the query. If I understand well, it lists all documents with a given patent. Thus...
The given parent is the parent of the "current document" (the document including the script.) If the script is called from Main.ChildDoc and ChildDoc has a parent called ParentDoc then Velocity sees ${doc.parent} and replaces with Main.ParentDoc before the query ever reaches the database. The doc.id was a mistake and the line should read #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.fullName='${doc.parent}' order by doc.title asc")) But we should all get in the habit of avoiding the dynamic building of queries because it can be a security issue. Think if doc.parent = "'; drop table xwikidoc;" Hibernate won't allow that but there are sometimes ways to fool even Hibernate. Best practice for query security is to use: #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.fullName=? order by doc.title asc", [$doc.parent])) Caleb
{{velocity}} #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")) #foreach($item in $results) #set ($itemdoc = $xwiki.getDocument($item)) [$item>$item], #end {{/velocity}}
must list all documents having ${doc.parent} as parent. So, included in a page ICT.WebHome, must list all child documents having ICT.WebHome as parent, mustn't it?
Well, I get nothing by including this snippet in such ICT.WebHome page. And there are 50 documents that have this age as parent page!
Please, where am I wrong?
One more things. Please, how is it possible to compare doc.id and $doc.parent? As far as I understand, doc.id maps to XWD_ID in xwikidoc table (it is a bigint(20) field). And $doc.parent invokes getParent() and gets a string in the form Space.Document. Thus, how is it possible to compare both values?
Thanks for your help!
Thank you, Chris
On Thu, Nov 4, 2010 at 3:36 AM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [email protected]<ml-node%[email protected]>
wrote:
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the
following
query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}'
order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly braces to wrap doc.parent?
Thanks!
is returning the expected set of documents, but the title order is
erratic.
The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have
'A',
'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these
spans,
no items appear out of order, but the 'span' separation is not expected
or
desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5704373&i=0> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @ http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5704... To unsubscribe from Unexpected 'Order By' Results, click here<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==>.
Hi! Thanks both! Caleb James DeLisle wrote:
On 11/05/2010 06:53 PM, [Ricardo Rodriguez] eBioTIC. wrote:
Hi!
Chris Wagner wrote:
Hello,
Sorry for the delay in reply.
Sergiu, that was the case. Thank you very much. :)
Ricardo, the query is to include a list of all child documents in order. I included the curly braces in the query simply because I find it easier to read.
Thanks, Chris. Far clear the reason to use curly braces, but I am not able to understand the query. If I understand well, it lists all documents with a given patent. Thus...
The given parent is the parent of the "current document" (the document including the script.) If the script is called from Main.ChildDoc and ChildDoc has a parent called ParentDoc then Velocity sees ${doc.parent} and replaces with Main.ParentDoc before the query ever reaches the database.
The doc.id was a mistake and the line should read #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.fullName='${doc.parent}' order by doc.title asc"))ç
The other way round :-) doc.parent='${doc.fullName}' I understand the simple logic of the query and got it working. Here a simple snippet using 2.0 syntax for simpler reference: {{velocity}} **These are the children of the current page** #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.parent=? order by doc.title asc", [$doc.fullName])) #foreach($item in $results) #set ($itemdoc = $xwiki.getDocument($item)) [[$itemdoc>>$itemdoc]] #end {{/velocity}}
But we should all get in the habit of avoiding the dynamic building of queries because it can be a security issue. Think if doc.parent = "'; drop table xwikidoc;" Hibernate won't allow that but there are sometimes ways to fool even Hibernate.
Best practice for query security is to use: #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.fullName=? order by doc.title asc", [$doc.parent]))
Caleb
I'm still struggling to understand the SQL injection attack as explained here... http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Security#HMitigationMeth... I'll create a simple new snippet entry once I understand it! Thanks!
{{velocity}} #set($results=$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}' order by doc.title asc")) #foreach($item in $results) #set ($itemdoc = $xwiki.getDocument($item)) [$item>$item], #end {{/velocity}}
must list all documents having ${doc.parent} as parent. So, included in a page ICT.WebHome, must list all child documents having ICT.WebHome as parent, mustn't it?
Well, I get nothing by including this snippet in such ICT.WebHome page. And there are 50 documents that have this age as parent page!
Please, where am I wrong?
One more things. Please, how is it possible to compare doc.id and $doc.parent? As far as I understand, doc.id maps to XWD_ID in xwikidoc table (it is a bigint(20) field). And $doc.parent invokes getParent() and gets a string in the form Space.Document. Thus, how is it possible to compare both values?
Thanks for your help!
Thank you, Chris
On Thu, Nov 4, 2010 at 3:36 AM, [Ricardo Rodriguez] eBioTIC. [via XWiki] < [email protected]<ml-node%[email protected]>
wrote:
Sergiu Dumitriu wrote:
On 11/03/2010 07:47 PM, Chris Wagner wrote:
Hello,
I am having some issues with the "order by" clause in HQL - the
following
query:
$xwiki.searchDocuments("where doc.hidden = false and doc.id='${doc.parent}'
order by doc.title asc")
Please, Chris, what this query is designed for? And why do you use curly braces to wrap doc.parent?
Thanks!
is returning the expected set of documents, but the title order is
erratic.
The documents are reordered, but it is not alphabetized as anticipated. Instead, there are several alphabetized "spurts" -- for example, I have
'A',
'C', 'P', ... 'A', 'A', 'B', 'C', 'D', 'E', etc. Within one of these
spans,
no items appear out of order, but the 'span' separation is not expected
or
desired. Is there a common issue that I could be overlooking?
There are two types of titles.
Document titles are stored in the database as the doc.title field, and they are editable in the editor above the content.
Display titles are computed from the document title, first heading from the content, or the document name, depending on which one exists, in this order. Unfortunately it is not possible to see the display title in queries.
I'm not sure, but I think that your problem is caused by this difference between display and document titles. The first set of documents has an empty document title, but in the UI you see their computed display title. The second set of documents is correctly ordered by their persisted document title.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
_______________________________________________ users mailing list [hidden email] <http://user/SendEmail.jtp?type=node&node=5704373&i=0> http://lists.xwiki.org/mailman/listinfo/users
------------------------------ View message @ http://xwiki.475771.n2.nabble.com/Unexpected-Order-By-Results-tp5702753p5704... To unsubscribe from Unexpected 'Order By' Results, click here<http://xwiki.475771.n2.nabble.com/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=5702753&code=ZWVsaG92ZXJjcmFmdEBnbWFpbC5jb218NTcwMjc1M3wtMTMzMDQxMjEwMQ==>.
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
participants (4)
-
[Ricardo Rodriguez] eBioTIC. -
Caleb James DeLisle -
Chris Wagner -
Sergiu Dumitriu