0

I have a database table with categories for different products in it. Each category has only one parent and each product can have multiple categories. I need to build a sort of breadcrumb navigation for a data migration, but can't figure out how to map the data so that I don't have tonds of queries.

The database is setup like so:

id     category              sort     parent
1      Home                  0        0
58     Car & Truck           4        1
135    10' Wide Shelters     0        58

Now I need to get the data back like this: Home/Car & Truck/10' Wide Shelters.

Is this possible with pure SQL or do I need to mix-in ColdFusion to get it. If I can do this with pure SQL then how would I do that, and if I can't then what would the ColdFusion look like?

orangepips
  • 9,891
  • 6
  • 33
  • 57
Dave Long
  • 9,569
  • 14
  • 59
  • 89
  • 2
    Investigate this question - http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database - specifically MySQL session variables - and GROUP_CONCAT(): http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat – orangepips Jun 14 '11 at 01:34
  • +1. Very thorough thread. Just curious - why a "comment" versus an "answer" ? – Leigh Jun 14 '11 at 03:56
  • @Leigh: I like my answers to actually solve the problem if possible. In this case I would want to include the exact SQL to make it happen, but seeing as I don't have MySQL setup anywhere I decided to provide a pointer that should lead to an answer. If OP decides it is worthy as an answer I'll post as such to get the points :). – orangepips Jun 14 '11 at 13:36
  • @orangepips - Understood. I tend to do the same as well. But that is a *great* thread on the topic. IMO, if you do not find an answer there ... you are just not paying attention ;) – Leigh Jun 14 '11 at 16:27

2 Answers2

0

Other RDBMSes support various ways of solving this problem (recursive with in ANSI SQL, connect by in Oracle, etc). But in MySQL, you're pretty much left with nested sets.

Josh
  • 992
  • 5
  • 5
0

Using nested sets, as recommended by user349433 I was able to come up with this query

SELECT c1.id AS id1, c1.category AS name1, c2.id AS id2, c2.category AS name2, c3.id AS id3, c3.category AS name3, c4.id AS id4, c4.category AS name4, c5.id AS id5, c5.category AS name5
FROM category c1
LEFT JOIN category AS c2 ON c2.parentid = c1.id
LEFT JOIN category AS c3 ON c3.parentid = c2.id
LEFT JOIN category AS c4 ON c4.parentid = c3.id
LEFT JOIN category AS c5 ON c5.parentid = c4.id

My data never goes deeper than 5 levels (I verified by checking with c6 and getting nulls). From here I can query against this

SELECT (name1 + '/' + name2 + '/' + name3 + '/' + name4 + '/' + name5) AS category
FROM getCats
WHERE
    <cfloop query="Arguments.assignments">
        (id1 = #Arguments.assignments.categoryid# OR id2 = #Arguments.assignments.categoryid# OR id3 = #Arguments.assignments.categoryid# OR id4 = #Arguments.assignments.categoryid# OR id5 = #Arguments.assignments.categoryid#)
        <cfif Arguments.assignments.currentrow IS NOT Arguments.assignments.recordCount> OR </cfif>
    </cfloop>

I already had a query of products and their category ids to loop over.

Dave Long
  • 9,569
  • 14
  • 59
  • 89
  • Re: *Using nested sets*... Do you mean you have another table that uses nested sets? (What is posted is the adjacency model) – Leigh Jun 15 '11 at 00:41
  • I'm sorry, I mis-read the mysql article. It is not a nested set, but just the join. – Dave Long Jun 15 '11 at 14:27
  • Okay. It is still a good reference to keep in mind for the future ;) Nested sets can work well for menus. – Leigh Jun 15 '11 at 15:43