[xwiki-devs] HQL queries to select tag - xwiki-devs@xwiki.org

Jean-Vincent Drean

28 Aug 28 Aug

4:18 p.m.

On Thu, Aug 28, 2008 at 4:08 PM, Bruno Neves <bneves(a)gedi.pt> wrote:

Greetings My objective is to list every document with some tag (e.g. "MainTag"), and next, select all tags of these documents without repeating any of them (including "MainTag"). I tried to merge the next two queries but without sucess. This query selects every document that have the tag "KB-4D": #set($sql = ", BaseObject as obj, DBStringListProperty as prop where obj.name=doc.fullName and obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and 'KB-4D' in elements(prop.list) order by doc.name asc") #set ($list = $xwiki.searchDocuments($sql)) This query selects every tags present in the documents listed by the previous query: #set($sql = "select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.name='$item'") #set ($tags = $xwiki.search($sql)) Can anyone help me with this?

This should do the trick (require programming rights) : #set($sql = "select distinct elements(prop.list) from XWikiDocument as doc, BaseObject as obj, DBStringListProperty as prop where obj.name=doc.fullName and obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and 'KB-4D' in elements(prop.list) order by doc.name asc") #set ($list = $xwiki.search($sql))

...

PS: How can I put snippet code on code.xwiki.org? I need to login but I am not registered.

You can register from http://www.xwiki.org -- Jean-Vincent Drean

Reply

Ludovic Dubost

4:57 p.m.

With Raffaello we found a MySQL bug that can make this query return an empty result set in MySQL 5. There is a workwround by writing the query this way #set($sql = ", BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.id=prop.id.id and prop.id.name='tags' and list = 'KB-4D'") The same type of join or an in query should probably be used instead of using elements(prop.list) which seems to generate some sql that MySQL does not want to handle propertly. So this would be: #set($sql = "select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.name in (select obj1.name from BaseObject as obj1, DBStringListProperty as prop1 join prop1.list list where obj1.id=prop1.id.id and prop1.id.name='tags' and list = 'KB-4D')") #set ($tags = $xwiki.search($sql)) Ludovic Jean-Vincent Drean wrote:

...

On Thu, Aug 28, 2008 at 4:08 PM, Bruno Neves <bneves(a)gedi.pt> wrote:

Greetings My objective is to list every document with some tag (e.g. "MainTag"), and next, select all tags of these documents without repeating any of them (including "MainTag"). I tried to merge the next two queries but without sucess. This query selects every document that have the tag "KB-4D": #set($sql = ", BaseObject as obj, DBStringListProperty as prop where obj.name=doc.fullName and obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and 'KB-4D' in elements(prop.list) order by doc.name asc") #set ($list = $xwiki.searchDocuments($sql)) This query selects every tags present in the documents listed by the previous query: #set($sql = "select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.name='$item'") #set ($tags = $xwiki.search($sql)) Can anyone help me with this?

This should do the trick (require programming rights) : #set($sql = "select distinct elements(prop.list) from XWikiDocument as doc, BaseObject as obj, DBStringListProperty as prop where obj.name=doc.fullName and obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and 'KB-4D' in elements(prop.list) order by doc.name asc") #set ($list = $xwiki.search($sql))

PS: How can I put snippet code on code.xwiki.org? I need to login but I am not registered.

You can register from http://www.xwiki.org

-- Ludovic Dubost Blog: http://blog.ludovic.org/ XWiki: http://www.xwiki.com Skype: ldubost GTalk: ldubost

Reply

Artem Melentyev

29 Aug 29 Aug

7:27 a.m.

New subject: [xwiki-devs] Re(2): HQL queries to select tag

Bruno Neves wrote:

...

Thank you so much so far I tagged three pages with this set of tags 1.º page: PRINT|KB-4D 2.º page: ARQUIVO|KB-FRAMEWORK 3.º page: PERSISTENCE|KB-FRAMEWORK|KB-4D After run the query you wrote I get the expected result: - KB-4D - KB-FRAMEWORK - PERSISTENCE - PRINT I tried to integrate a criteria expression "not like 'KB-%' in the query to filter the first two tags (KB-4D, KB-FRAMEWORK) useless for the final porpose (select all the subjects of a knowledge base) but without sucess. Can you help me in this? The main reason that keeps me far from my objective is not understand the mapping of XWiki database or how it's implemented. Can you give some information or documents you already have?

Look at http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples and http://platform.xwiki.org/xwiki/bin/view/DevGuide/DatabaseSchema -- Artem Melentyev

Reply

adoro

1 Sep 1 Sep

12:17 p.m.

Thanks a lot, it solved my problem with MySql too! (S. http://n2.nabble.com/Tag-search-returns-no-results-td786999.html#a786999) Best Regards, Alla Ludovic Dubost-2 wrote:

...

With Raffaello we found a MySQL bug that can make this query return an empty result set in MySQL 5. There is a workwround by writing the query this way #set($sql = ", BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.id=prop.id.id and prop.id.name='tags' and list = 'KB-4D'")

-- View this message in context: http://n2.nabble.com/HQL-queries-to-select-tag-tp789666p796970.html Sent from the XWiki- Dev mailing list archive at Nabble.com.

Reply