MySQL Join Question

Forum Moderators: open

Message Too Old, No Replies

MySQL Join Question

A MySQL description involving sub elements of a tree structure

Sekka

3:54 pm on Apr 23, 2007 (gmt 0)

Hi,

I have this table (simplified for the sake of the eyes),

CREATE TABLE IF NOT EXISTS `pre_galleries_tree` (
 `tree_id` int(11) auto_increment,
 `gallery_id` int(11),
 `tree_subof` int(11),
 PRIMARY KEY (`tree_id`),
 KEY `gallery_id` (`gallery_id`,`tree_subof`)
);

This is a essentially a tree structure for a gallery. If you want a certain gallery to be a sub-gallery for another gallery, you simply put the parent gallery ID in the 'tree_subof' field.

Now, how would I do a query to pull every gallery that is a sub gallery of a certain gallery ID, and any sub galleries of those galleries, and so on? Is this possible in one query?

Please tell me if that makes little or no sense.

Thank you.

joelgreen

9:00 pm on Apr 23, 2007 (gmt 0)

SQL does not support recursion, so it cannot be done in one query. In order to get such results in one query you would have to change/improve the way you store data.

Possible solution could be saving whole path to the node in additional field.

gallery_path = "3,34,455"

would mean gallery 455 (current) has parent 34, which has parent 3

All selects become simple.
Now you would be able to select all sub-galleries of gallery 3 or 34 with simple sql query.

Downside: you'll have to update those keys if node changes parent.

coopster

1:24 am on Apr 27, 2007 (gmt 0)

Exactly. It's called "adjacency list" versus "nested set" models. This page at MySQL might help

[dev.mysql.com...]

The model works well on one hand, on the other it is a pain in the rear. You have to decide whether or not you want to handle the recursion at the database level or the application level.

There are good arguments for both, you'll have to do some serious reading to figure out what works best for you.