Hey, hopefully someone can help my absent memory on this one. Awhile ago I had looked at how to do this with Cf-ORM and was fairly successful with it. Now after god knows how many years, I am looking into it again.
The biggest problem is there is very little out there on it, well ones that are worth reading any way.
In code I found, this seems to work. But fails on the depth check.
savecontent variable=“local.hqlQuery” {
writeOutput("
FROM fbCategory as category
where (
select Count(categorySubQuery)
from fbCategory categorySubQuery
where categorySubQuery.left < category.left and categorySubQuery.right > category.right) < 2
order by category.left
");
}
return ORMService.executeQuery(query=local.hqlQuery, asQuery=true);
It seems that the where clause is not working and ends up returning everything. I tried to reverse it back to SQL and had no luck even getting it to work there either.
Any help would be appreciated.
Andrew,
At a basic level, I’ve had a lot of success drawing a diagram of the problem I’m trying to solve. The logic of the SQL is much easier to grasp in that way.
“nested sets joe celko” is the Google search phrase you need, in case you haven’t run across that. Joe Celko is the expert on this.
Here’s one result, perhaps it’s enough:
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
Sorry Nando, none of that is helpful at all, that is for SQL not HQL.
I fully understand how it works, I just can’t get my HQL working the way it should. For example here is the code in normal SQL.
SELECT node.forumId, node.name, node.parentId, node.leftId, node.rightId, COUNT(parent.name) - 1 AS depth
FROM fb_forum AS node CROSS JOIN
fb_forum AS parent
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId)
GROUP BY node.forumId, node.name, node.parentId, node.leftId, node.rightId
ORDER BY node.leftId
In this, it retrieves all the categories and their depth. In what I am trying to do is limit that to a depth of less than 2. So that I only get the top parent and the immediate children only. trust me I have been all over google the last 3 days trying to work out why the HQL is not working.
Then the question that comes to my mind is that since HQL generates SQL, why stick to HQL’s limitations when you can correctly write the SQL yourself?
To me, the abstraction that ORM provides is great, until it gets in your way.
Hmmmm…
“It seems that the where clause is not working and ends up returning everything. I tried to reverse it back to SQL and had no luck even getting it to work there either.”
I just can’t seem to get the query right to limit by depth.
So you are saying that this query works, but it is to unlimited depth:
SELECT node.forumId, node.name, node.parentId, node.leftId, node.rightId, COUNT(parent.name) - 1 AS depth
FROM fb_forum AS node CROSS JOIN
fb_forum AS parent
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId)
GROUP BY node.forumId, node.name, node.parentId, node.leftId, node.rightId
ORDER BY node.leftId
and that if you add a statement to the where clause limiting the depth, something like:
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId) and depth < 2
it doesn’t work as you need it. Am I understanding you correctly?
That is correct it throws an error
And what does the error indicate?
that depth is not defined
Because the top level doesn’t have a parent?
I don’t think you can reference an alias in the where clause. You need to do “AND COUNT(parent.name) - 1 < 2”.
Alternatively, wrap the base query in a sub-select or use a CTE.
Thanks!
~Brad
ColdBox Platform Evangelist
Ortus Solutions, Corp
E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com
Brad,
No you can’t, which is where I got stuck. I tried a sub select and just got myself into more trouble, but at the end of it all I require it in HQL any way.
So does this work?
SELECT node.forumId, node.name, node.parentId, node.leftId, node.rightId, COUNT(parent.name) - 1 AS depth
FROM fb_forum AS node CROSS JOIN
fb_forum AS parent
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId) AND COUNT(parent.name) - 1 < 2
GROUP BY node.forumId, node.name, node.parentId, node.leftId, node.rightId
ORDER BY node.leftId
My comment “Top level doesn’t have a parent” asks if COUNT(parent.name) is undefined at the top level of the tree.
It works, in that it returns all the records and assigns the depth as part of the query, yes. But I already said that and that it is when I had the clause to restrict it to a defined depth count that it wont work.
For example I came across this article, where they are doing exactly what I want to do.
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
I had to make changes to it, which ended up like this.
SELECT node.name, COUNT(parent.name) - (sub_tree.depth + 1) AS depth
FROM (SELECT TOP (100) PERCENT node.name, COUNT(parent.name) - 1 AS depth
FROM category AS node CROSS JOIN
category AS parent
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId)
GROUP BY node.name, node.leftId
ORDER BY node.leftId) AS sub_tree INNER JOIN
category AS sub_parent ON sub_tree.name = sub_parent.name CROSS JOIN
category AS node CROSS JOIN
category AS parent
WHERE (node.leftId BETWEEN parent.leftId AND parent.rightId) AND (node.leftId BETWEEN sub_parent.leftId AND sub_parent.rightId)
GROUP BY node.name, node.leftId, sub_tree.depth
HAVING (sub_tree.depth <= 1)
ORDER BY node.leftId
Notice the having clause, well that seems to be totally ignored for some reason. Stupid thing about this is that I actually had it working a few years ago. Just can’t find the application I had written it in to pull it out. But like I said I need this in HQL foremost.
kk I got the SQL working again, now it back to what I think I had which is this.
SELECT Id, leftId, rightId, name, parentId
FROM category AS parent
WHERE ((SELECT COUNT(name) - 1 AS name
FROM category AS child
WHERE (parent.leftId BETWEEN leftId AND rightId)) < 2)
ORDER BY leftId
Which is why I have the HQL like this, so this should work but alas it doesn’t.
savecontent variable=“local.hqlQuery” {
writeOutput("
FROM category as parent
where (
select Count(name)-1
from category child
where child.left BETWEEN parent.left AND parent.right) < 2)
order by parent.left
");
}
return ORMService.executeQuery(query=local.hqlQuery, asQuery=true);
so no one knows why this won’t work in HQL?
Reply inline:
kk I got the SQL working again, now it back to what I think I had which is
this.
SELECT Id, leftId, rightId, name, parentId
FROM category AS parent
WHERE ((SELECT COUNT(name) - 1 AS name
FROM category AS child
WHERE (parent.leftId BETWEEN leftId AND
rightId)) < 2)
ORDER BY leftId
Good.
savecontent variable="local.hqlQuery" {
writeOutput("
FROM category as parent
where (
select Count(name)-1
from category child
where child.left BETWEEN parent.left AND parent.right) < 2)
order by parent.left
");
}
return ORMService.executeQuery(query=local.hqlQuery, asQuery=true);
Now the question is what SQL does the above HQL generate? I assume you have
set logsql="true" in your ORM settings and are looking at the generated SQL
to see what is occurring under the covers.
How does it differ from the SQL you need it to generate, as above?
grrrrrr
Nando, don’t you think I would have gone through all that. I am a huge advocate of logs and have gone through them and yes there is a very slight difference. But I don’t know how to fix the HQL to generate the same SQL from the HQL.
So I am back to my original question, what the hell is going on?
btw the only difference is this
where
(
select
count(category1_.name)
from category category1_
where
category0_.[left] between category1_.[left] and category1_.[right]
) < 2
If you look at the original SQL it has another outer brackets enclosing the where clause, even if I add this to the HQL it is automatically removed.
But I appreciate pointing me to something, I have already done.
** FacePalm **
Sorted, it appears that when you make changes to an Enity. It would pay to actually delete the table and start again, on closer inspection at some point in the development the Entity had changed from leftId to left. So it had both columns and the data was being searched in the new fields and the data was actually in the old fields.