Help me with some database stuff here..

[screw3d]

Here's the simplified schema:

Inventory
------------
productId
cat_id
name
...

Category
------------
cat_id
cat_father_id
cat_name

Cats_Idx <-- no idea what this means
-----------
id
cat_id
productId


Here's what I want to do:
Given a category number (cat_id), I want to select all products from all the child (and grandchild etc) categories of that cat_id.

I want this to be done in a single MySQL query.. but if it's not possible, then I'll take alternatives.

Caveat:
You cannot change the schema.. otherwise I would have to change a whole bunch of other stuff. If you really want to know, this is from CubeCart 3.0.14, in includes/content/content/viewCat.inc.php.

Edit:
Found out another way to do this within the application.

Thanks!

 

[MrChad]

I'm assuming the hierarchy can be n levels deep? If so, there's no way to capture all of the products in a single query.

You might be able to use a stored procedure, but a recursive PHP function is the easiest way of doing it.

 

[BoberFett]

I remember looking into hierarchal queries a number of years ago and seeing that Oracle could do it. Kinda doubt MySQL can do it though.

 

[screw3d]

Originally posted by: MrChad
I'm assuming the hierarchy can be n levels deep? If so, there's no way to capture all of the products in a single query.

You might be able to use a stored procedure, but a recursive PHP function is the easiest way of doing it.

Yeah, it should work for n levels, but practically, it probably won't be over 2-3 levels. I guess I can figure out the recursion. Thanks!

 

[screw3d]

Originally posted by: BoberFett
I remember looking into hierarchal queries a number of years ago and seeing that Oracle could do it. Kinda doubt MySQL can do it though.

I think MySQL did just introduce stored procedures.. but I have no experience working with that and I'm not a DB guy

 

[MrChad]

Originally posted by: BoberFett
I remember looking into hierarchal queries a number of years ago and seeing that Oracle could do it. Kinda doubt MySQL can do it though.

Cool. I just read up a little bit on Oracle's CONNECT BY PRIOR command; neat stuff.

http://rpbouman.blogspot.com/2005/08/slight-connect-by-problemsolved.html

I haven't been able to find any info on a MySQL equivalent, although it looks like it would accomplish exactly what the OP wants.

 

[BoberFett]

Yep, that's the one. Very cool piece of tech which can take an ugly stored proc and turn it into a nice little query. I never got to use it though, as the company I was with at the time refused to move away from MS SQL.

 

[screw3d]

This is a very common thing to solve no? I wonder how all the MySQL based software are doing it now..

Edit: thanks for the link MrChad..

So this was what I read before:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

that's why I mentioned I couldn't change my schema to have something like that

--
Ah OK. So hierarchical data in DB is not trivial at all I was beginning to think I'm retarded after attempting at the problem for the past 2 hours

 

[wtf242]

Impossible without changing the schema. The correct way to implement something like this in SQL is with an Adjacency List.

http://en.wikipedia.org/wiki/Adjacency_list
http://www.sqlsummit.com/AdjacencyList.htm

 

[ahurtt]

Originally posted by: screw3d


Cats_Idx <-- no idea what this means
-----------
id
cat_id
productId

Probably stands for "categories index" or something. Id is an identity column, cat_id and productId are foreign keys pointing to the corresponding column names from the other 2 tables respectively.

As for a solution to your problem. . .I dunno. . .thinking on it.

 

[screw3d]

I just realized that the software works around this by assigning multiple categories to a product. Say I have Electronics -> TV -> LCD -> Sony XXX model, I will have to assign all 3 categories to that product. It's not very elegant at all but oh well, it works. I'll have some explaining to do to the client :/

BTW I don't know if I can recommend this cart to anyone.. it's not OSS although free (as in beer) with some limitations. The code base is total sh1t. Some stuff that the developer did doesn't make any sense.. but then it's about the best among all the ones I've looked at (OSC, zen cart etc)