Recursive SELECT?

Hi Chaps,

I’m not sure if this is a recursive select query or something else.

Basically, I have a table of clothing products and also have a table of product links. The product links are used to link similar clothing products together.

e.g.
tbl_prod_links (id, parent_id, child_id)

I want to build a select query that selects all ‘linked’ products of a given product_id.
But on top of that, I want to select the linked products of each of the query’s child_id’s…if that makes sense?

Many thanks

Samuel

nope, sorry

perhaps you could give some illustrative rows of sample data, and then show what results you want from the query

so you’ll want to do a type of self-join query, using the many-to-many table as an intermediary. essentially:


select p.*, group_concat(concat(p2.id,'|',p2.name)) as child_products
from tbl_products as p
left join tbl_prod_links as l on p.id = l.parent_id
left join tbl_products as p2 on l.child_id = p2.id
group by p.id

By grouping with a concat/group_concat, you get one record per main product that matches your query, and can pull only the necessary data of the related products. This is a high-performance query, and you’ll be able to split the child_products field for use in your app.

You don’t have to group them, but you’ll get duplicates of the main product if you don’, so you’ll have to handle that in your code. You’ll also deal with a lot of unnecessary output from your DB, which is why I prefer the group_concat version.

transio, he wanted not only related products, but also related products of those related products (like this)

Just add another level of recursion with a p3 alias. Sorry on Phone in car will post when I get back.

already done, please see link i posted

Gotcha, Rudy, was in my car… couldn’t go see that. That’s definitely a good solution, though I personally prefer the group_concat way of doing it (as I mentioned above), so you get one record per main entry, e.g.:


select p.*, 
    group_concat(concat(p1.prod_id,'|',p1.prodtitle) separator '##') as children,
    group_concat(concat(p2.prod_id,'|,p2.prodtitle) separator '##') as grandchildren
from tbl_products as p
left join tbl_prod_links as l1 on p.prod_id = l1.parent_id
left join tbl_product as p1 on l1.child_id = p1.prod_id
left join tbl_prod_links as l2 on p1.prod_id = l2.parent_id
left join tbl_product as p2 on l2.child_id = p2.prod_id
group by p.id

It executes quicker, parses with less code in your app, transmits less data between DB and app server, and is a simpler query overall.

To use it, you simply loop through each product, and split the children and grandchildren by “##” to retrieve arrays of related products, then split products by “|” to get the id / title columns.

Cheers. :slight_smile:

Hey Rudy,

Just saw on the other forum that he doesn’t want two levels of recursion… he’s actually looking for children and parents of the current product (not children and grandchildren), so something like this:


select p.*, 
    group_concat(concat(p1.prod_id,'|',p1.prodtitle) separator '##') as children,
    group_concat(concat(p2.prod_id,'|,p2.prodtitle) separator '##') as parents
from tbl_products as p
left join tbl_prod_links as l1 on p.prod_id = l1.parent_id
left join tbl_product as p1 on l1.child_id = p1.prod_id
left join tbl_prod_links as l2 on p.prod_id = l2.child_id
left join tbl_product as p2 on l2.parent_id = p2.prod_id
group by p.id

Maybe repost to the other forum so he gets it?

Cheers.

be my guest :smiley:

LOL… I really don’t need another distraction right now. 3 forums take away enough of my time from work! I guess he’ll come back here eventually. Heh. :smiley:

Ahh, wow, thanks for the replies guys, some reason, the subscribe to topic didn’t work. been fretting over this of days! i’ll check these out, thanks!

OK, thanks for the replies chaps, I’ve tested both queries, but still not getting the correct results:

With the select query you posted, if I select either 1 or 2, then I get some results, but if I select 3 or 4, because they are not parent_IDs, then I get no results.

CREATE TABLE `tbl_product` (
  `prod_id` int(10) unsigned NOT NULL auto_increment,
  `prodtitle` varchar(255) default NULL,
  PRIMARY KEY  (`prod_id`)
);

INSERT INTO `tbl_product` VALUES (1,'Blue Jeans'),(2,'White Top'),(3,'Green Shirt'),(4,'Black Hat'),(5,'Pink Dress');

CREATE TABLE `tbl_prod_links` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned default NULL,
  `child_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `child_id` (`child_id`)
); 

INSERT INTO `tbl_prod_links` VALUES (1,1,2),(2,1,3),(3,2,4);

This would mean that if I have products page where I’ll looking at the Blue Jeans (1), the products linked to the jeans would be:
2: White Top - as it is linked prod_link[id=1]
3: Green Shirt - as it is linked prod_link[id=2]
4: Black Hat - as it is linked prod_link[id=3], which is linked to prod_link[2]

If I select item 1, 2, 3 OR 4, then 3 linked items would show (so in effect I’ll be searching for parent_IDs and child_IDs of each result (recursion?)), and if I select 5, then nothing shows, as it is not linked to anything else?

pokes nose in
so… 1 product can be 1 parent and have 1 child. Either or both may be NULL.
You want to pull the entire chain every time.

Why… Okay i’m going to make myself sound foolish here a bit, but it sounds to me like you’re overcomplicating this if the order of items does not necessarily matter. Assign the products to a Category (the chain), and pull a simple query on the category ID. (And if the order does matter, add an Order field to sort the chain in the order you want. Natural Key (categoryID,order) )

Thanks for the input, but I’m not sure if I’m getting what I want across.

New Simple Example:
Item 1 is linked to Item 2
Item 3 is linked to Item 2
Item 4 is not linked to anything.

If I select Item 1, then Item 2 and Item 3 are selected as similar products.
If I select Item 2, then Item 1 and Item 3 are selected as similar products.
If I select Item 3, then Item 1 and Item 2 are selected as similar products.
If I select Item 4, then nothing is selected as similar products.

From your example.
Item 1, 2, and 3 belong to Category 1.
Item 4 belongs to Category 2.

If you’re looking at product 1,
SELECT * FROM table WHERE categoryID = 1 AND productID != 1 returns 2,3. (The productID != 1 is used to prevent it from pulling the product you’re currently looking at.)

Hi StarLion, Don’t want to use this category system, as there might be some items that aren’t meant to be linked together appearing in the results. If I can link two items together, and then select the ‘chain’ of all of their ‘linked’ items, I will then be able to select ‘similar’/‘linked’ items.

“as there might be some items that aren’t meant to be linked together appearing in the results.”

I… dont understand this statement. Can you give me an example of how two things that arnt meant to be linked together would be put in the same category?

“as there might be some items that aren’t meant to be linked together appearing in the results.”

OK, I guess I was thinking of a limited number of Categories, which I want to avoid maintaining.
I don’t really want to create Categories and then assign different items to each/multiple.
If this is the best way of doing it, then I guess I’ll have no option. But I’d really like to find a way of selecting a chain of linked items, and then find all linked items of the linked items.

“I don’t really want to create Categories and then assign different items to each/multiple.”

But you want to create items and assign different items as links to each… dont… really see the difference there, but whatever floats your boat i guess.

The trouble is that mysql isnt really designed for recursion queries like you’re wanting to do (recurse potentially 2 trillion times to exhaust all possibilities). If you’re really going to insist on it, as far as i know you’ll have to execute multiple queries through a recursive scripting language such as PHP.

Thanks, that’s what I thought/feared.
The reason for trying to avoid creating/maintaining Categories, is that the products that are being sold are one-off’s. So it’ll be a quick way of grouping similar items by linking them together, product-to-product, rather than new category, then assigning each product to the new category…
I’ll look into the Category solution and see if it’s viable, if not, I’ll repost to a PHP forum. Thanks