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开发者_JAVA百科 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?
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.
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.