So I’ve got a ‘entry’ listing application that my company uses. All entries are categorized based on a table with id, title, parent_id.
My problem is on the search page, I was asked to create breadcrumbs of where the entry is located.
My first attempt (horrible) was as I was outputting the search results I had a loop that kept hitting the db grabbing a category title based on the parent_id of the category before it only stopping when it hit a category with a 0 or NULL as a parent_id. This sent the db out of control.
Second attempt, I loaded the entire category table with a cfquery call. Then using QOQ i basically did the same thing. THIS send BlueDragon out of control, eventually consuming 90+% of CPU resources, creating the need for a restart.
Is there a better way to go about doing this that I’m not thinking of? The only other solution to this was presented to me by the server admin, to only show the category that the result is in (single query call per result), but I think the breadcrumb approach would be more beneficial.
Depending on how your db is structured you can join your table to itself, like so:
SELECT
top.id AS topID,
top.name AS topName,
mid.id AS midID,
mid.name AS midName
FROM categories AS top
INNER JOIN categories AS mid
ON mid.childOf = top.id
WHERE top.childOf = 0
The structure on your table would look like this:
id int
name varchar
childOf int
and a sample data set might look something like this:
id name childOf
1 dream 0
2 design 0
3 develop 0
4 personal 0
5 AIR 3
6 AJAX 3
7 CSS 3
8 ColdFusion 3
9 Flash 3
10 Flex 3
11 Photoshop 2
12 Illustrator 2
13 Family 4
14 Apple 1
16 SQL 3
17 projects 3
And this is what the query above pulls, using a more complete data set:
topID topName midID midName
1 dream 14 Apple
2 design 11 Photoshop
2 design 12 Illustrator
2 design 15 From a former designer
2 design 28 Wallpapers
2 design 34 Adobe
2 design 43 Fireworks
3 develop 5 AIR
3 develop 6 AJAX
3 develop 7 CSS
3 develop 8 ColdFusion
3 develop 9 Flash
3 develop 10 Flex
3 develop 16 SQL
Rudy taught me that one and it’s been put to good use more times than I can count.
If your data is nested more levels deep, then you can add an additional join like so:
SELECT
top.id AS topID,
top.name AS topName,
mid.id AS midID,
mid.name AS midName,
child.id AS childID,
child.name AS childName
FROM categories AS top
INNER JOIN categories AS mid
ON mid.childOf = top.id
WHERE top.childOf = 0
Interesting SQL there, but what if you don’t know the levels? The tool is pretty much out of my control now. I gave them the ability to not only Add/Edit/Delete entries, but also the ability to add/edit/delete categories as they saw fit. So, results could vary from being 4 levels deep to 1 level deep.
Is this something where I just plan for the worst with JOINS for 4 (or whatever I choose to be the highest, css is only built for so many deep now that I think about it) and then test for NULL on output? Is that creating the same problem in another manner?
Is this something where I just plan for the worst with JOINS for 4 (or whatever I choose to be the highest, css is only built for so many deep now that I think about it) and then test for NULL on output? Is that creating the same problem in another manner?
Yes - assuming there’s a hard limit to the number of levels. As creole said, just add however many joins you need. In this case 4. Then handle any empty values in your output.
If there’s an unlimited number of levels… then you’re stuck with recursion or whatever functions your db has for recursion
it was on sitepoint forums many years ago (i can’t find the link at the moment) where someone tested the SQL that went 15 levels deep and confirmed that there is absolutely no problem in performance
there is, however, a different problem – the user interface
if you have 15 levels of hierarchy, how are you going to show where you are? your users will likely get confused
