[xwiki-users] using subqueries in HQL
Hello everybody! I need some help about construction of a subquery. I have 2 classes: projects and steps. Also, name of a projectclass pages - is one of a property of a class step (for this reason, If I know project, I can find all steps of this project). I have one query: #set ($hql = , BaseObject as obj, StringProperty as prop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.ProjectClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='ProjectName') order by prop.value asc ) This query returns all projects. After this, I use #foreach ($proj in $projs) #set ($projDoc = $xwiki.getDocument($proj)) #set ($projName = $projDoc.getName()) ... And, each time, for each project, I use second query, with additional filter by $projName: #set ($hql2 = , BaseObject as obj, StringProperty as prop, StringProperty as otherprop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.StepClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='Project' and prop.value='$projName') and (obj.id=otherprop.id.id and otherprop.id.name='Step') order by prop.value asc, otherprop.value asc ) Together, this construction returns to me list of all projects with all params, and for each project - list of all steps with all params. This construction works normal, but I know, that HQL give possibility to use subqueries. It is a best variant, because now 2-nd query runs for each project. If projects will be much more - speed will go down ... In a page with HQL examples (http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples) unfortunately not exist any examples about subqueries in a XWiki. Can somebody help me? Thanks beforehand! Eugen Colesnicov -- View this message in context: http://xwiki.475771.n2.nabble.com/using-subqueries-in-HQL-tp5069488p5069488.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
Re: [xwiki-users] using subqueries in HQL
Hi Eugen, Looks like you are looking for an HQL query that returns a map of (project - list of steps) pairs which is not possible. The result set is a flat structure. At most you can get a list of [project, step] arrays: [p1, s1] [p1, s2] ... [p2, s1] ... Why do you need to list all the projects with all their steps. Wouldn't be better to choose the project from a list and then get the steps for the selected project? Anyway, I don't see the need/usecase for a subquery. Hope this helps, Marius On 05/18/2010 02:12 PM, Colesnicov Eugen wrote: Hello everybody! I need some help about construction of a subquery. I have 2 classes: projects and steps. Also, name of a projectclass pages - is one of a property of a class step (for this reason, If I know project, I can find all steps of this project). I have one query: #set ($hql = , BaseObject as obj, StringProperty as prop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.ProjectClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='ProjectName') order by prop.value asc ) This query returns all projects. After this, I use #foreach ($proj in $projs) #set ($projDoc = $xwiki.getDocument($proj)) #set ($projName = $projDoc.getName()) ... And, each time, for each project, I use second query, with additional filter by $projName: #set ($hql2 = , BaseObject as obj, StringProperty as prop, StringProperty as otherprop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.StepClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='Project' and prop.value='$projName') and (obj.id=otherprop.id.id and otherprop.id.name='Step') order by prop.value asc, otherprop.value asc ) Together, this construction returns to me list of all projects with all params, and for each project - list of all steps with all params. This construction works normal, but I know, that HQL give possibility to use subqueries. It is a best variant, because now 2-nd query runs for each project. If projects will be much more - speed will go down ... In a page with HQL examples (http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples) unfortunately not exist any examples about subqueries in a XWiki. Can somebody help me? Thanks beforehand! Eugen Colesnicov ___ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] using subqueries in HQL
and btw http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples#HStatistics leads to some HQL examples with sub queries, using the protected search API though. Marius On 05/18/2010 05:10 PM, Marius Dumitru Florea wrote: Hi Eugen, Looks like you are looking for an HQL query that returns a map of (project - list of steps) pairs which is not possible. The result set is a flat structure. At most you can get a list of [project, step] arrays: [p1, s1] [p1, s2] ... [p2, s1] ... Why do you need to list all the projects with all their steps. Wouldn't be better to choose the project from a list and then get the steps for the selected project? Anyway, I don't see the need/usecase for a subquery. Hope this helps, Marius On 05/18/2010 02:12 PM, Colesnicov Eugen wrote: Hello everybody! I need some help about construction of a subquery. I have 2 classes: projects and steps. Also, name of a projectclass pages - is one of a property of a class step (for this reason, If I know project, I can find all steps of this project). I have one query: #set ($hql = , BaseObject as obj, StringProperty as prop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.ProjectClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='ProjectName') order by prop.value asc ) This query returns all projects. After this, I use #foreach ($proj in $projs) #set ($projDoc = $xwiki.getDocument($proj)) #set ($projName = $projDoc.getName()) ... And, each time, for each project, I use second query, with additional filter by $projName: #set ($hql2 = , BaseObject as obj, StringProperty as prop, StringProperty as otherprop where (obj.name=doc.fullName and obj.className='UAProjectManagementCode.StepClass' and obj.name not like '%ClassTemplate') and (obj.id=prop.id.id and prop.id.name='Project' and prop.value='$projName') and (obj.id=otherprop.id.id and otherprop.id.name='Step') order by prop.value asc, otherprop.value asc ) Together, this construction returns to me list of all projects with all params, and for each project - list of all steps with all params. This construction works normal, but I know, that HQL give possibility to use subqueries. It is a best variant, because now 2-nd query runs for each project. If projects will be much more - speed will go down ... In a page with HQL examples (http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples) unfortunately not exist any examples about subqueries in a XWiki. Can somebody help me? Thanks beforehand! Eugen Colesnicov ___ 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
