SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kool_samule View Post
    ...if that makes sense?
    nope, sorry

    perhaps you could give some illustrative rows of sample data, and then show what results you want from the query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so you'll want to do a type of self-join query, using the many-to-many table as an intermediary. essentially:

    Code:
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    transio, he wanted not only related products, but also related products of those related products (like this)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just add another level of recursion with a p3 alias. Sorry on Phone in car will post when I get back.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by transio View Post
    Just add another level of recursion with a p3 alias. .
    already done, please see link i posted
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.:
    Code:
    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.

  8. #8
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by transio View Post
    Maybe repost to the other forum so he gets it?
    be my guest
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  12. #12
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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?

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    *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) )

  14. #14
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.)

  16. #16
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  17. #17
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    "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?

  18. #18
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "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.

  19. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    "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.

  20. #20
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  21. #21
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    "rather than new category, then assigning each product to the new category..."

    No... you dont create a new category for each product... you add new products to existing categories. That way you dont have to change any of the existing products when you add a new one, or sell a current one.


    EX:

    Your chain is productIDs:

    1-2-4-5-8

    You sell product 4. Now you've got to do 3 things:
    Remove product 4.
    Update product 2 to point at product 5
    Update product 5 to point at product 2
    ...to maintain your chain.

    If all 5 of the products belong to category 1, you just... delete product 4. The rest of the products dont care. They still belong to category 1.

    If you need to add a product to your chain, you have to:
    find a chain member that doesnt already have a child.
    add the new product
    update the chain member from before to point at the new product

    Or...
    add the product with categoryID = 1

    Done.

  22. #22
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Agreed.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •