Mysql: Join Many Tables On One Statement

August 20, 2024 Post a Comment

i have the following DB-Structure / hierarchy: TABLE product_type: id,name, .... TABLE product: id,parent_id, name, ... parent_id : is the product_type id TABLE treeNode: id, par

Solution 1:

How to get all descendants from a tree node with recursive query in MySql?

It's really a problem for MySql, and it's a key point to this question, but you still have some choices.

Assuming you have such sample data, not as much as your sample but enough to demonstrate:

createtable treeNode(
id int, parent_id  int,  name varchar(10), type varchar(10),level int);
insertinto treeNode 
(id, parent_id, name, type, level) values 
( 1,  0,  'C1    ', 'CATEGORY', 1),
( 2,  1,  'C1.1  ', 'CATEGORY', 2),
( 3,  2,  'C1.1.1', 'CATEGORY', 3),
( 4,  1,  'C1.2  ', 'CATEGORY', 2),
( 5,  4,  'C1.2.1', 'CATEGORY', 3),
( 3,  8,  'G1.1.1',    'GROUP', 3),
( 4,  9,  'G1.2  ',    'GROUP', 2),
( 5,  4,  'G1.2.1',    'GROUP', 3),
( 8,  9,  'G1.1  ',    'GROUP', 2),
( 9,  0,  'G1    ',    'GROUP', 1);

First choice: level code

Like the sample data of the name column in treeNode table. (I don't know how to say it in English, comment me about the correct expression of level code.)

To get all descendants of C1 or G1 could be simple like this:

select*from treeNode where type ='CATEGORY'and name like'C1%' ;
select*from treeNode where type ='GROUP'and name like'G1%' ;

I prefer this approach very much, even need us to generate these code before treeNode saved in application. It will be more efficient than recursive query or procedure when we have large number of records. I think this is a good denormalization approach.

With this approach, the statement you want with join could be:

SELECTdistinct p.*--if there is only one tree node for a product, distinct is not neededFROM product p
JOIN product_type pt
     ON pt.id= p.parent_id -- to get product type of a productJOIN linked_TreeNode LC
     ON LC.product_id= p.id -- to get tree_nodes related to a productJOIN (select*from treeNode where type ='CATEGORY'and name like'C1%' ) C --may replace C1% to concat('$selected_cat_name','%')ON LC.treeNode_id = C.id
JOIN (select*from treeNode where type ='GROUP'and name like'G1%' ) G --may replace G1% to concat('$selected_group_name','%')ON LC.treeNode_id = G.id
WHERE pt.name ='$selected_type'-- filter selected product type, assuming using product.name, if using product.parent_id, can save one join by pt like your original sql

Sweet, isn't it?

Second choice: level number

Append a level column to treeNode table, as shown in the DDL.

Level number is much easier to maintain than level code in application.

With level number to get all descendants of C1 or G1 need a little trick like this:

SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select*from treeNode where type ='CATEGORY'orderby level) as t
  JOIN (select@pv:='1')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv);
 -- get all descendants of `C1`SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select*from treeNode where type ='GROUP'orderby level) as t
  JOIN (select@pv:=',9,')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv) ;

This approach is slower than the first, but still faster than recursive query.

The full sql to the question omitted. Only need to replace those two subquery of C and G with two querys above.

Note:

There are many similar approach such as here , here, or even here. They won't work unless ordered by level number or level code. You could test the last query in this SqlFiddle by changing order by level to order by id to see the differences.

Another Choice: The Nested Set Model

Please reference to this blog, I did not test yet. But I think it's similar to last two choice.

It need you add a left number and a right number to the treenode table to enclose all descendants' ids between them.

Solution 2:

This is not doable in MySQL as it is missing the feature that you need: Recursive querying.

Oracle can do this with a START WITH ... CONNECT BY statement.

You recurse over a table in a procedure and write the result in a temporary table. That table can be queried within the same session.

something like:

CREATEPROCEDURE products_by_cat_and_grp(typ INT, cat INT, grp INT)
BEGIN-- create temporary table which we query later onCREATE TEMPORARY TABLE tmp_products LIKE product;
ALTERTABLE tmp_products
ADD cat_id INT
, ADD grp_id INT;

-- first insert all products of the category and groupINSERTINTO  tmp_products
SELECT P.*, cat.id, grp.id
FROM linked_TreeNode lc 
JOIN product prd
  ON lc.product_id = prd.id
JOIN treeNode cat
  ON lc.treeNode_id = cat.id
JOIN treeNode grp
  ON lc.treeNode_id = grp.id
WHERE prd.parent_id = typ
  AND cat.id = cat
  AND grp.id = grp;

-- now we iterate over subcategories until there is nothing leftSET@rownum=1;

WHILE @rownum>0 DO
  CREATE TEMPORARY TABLE tmp_parents
  ASSELECTDISTINCT id, cat_id AS parent_id
  FROM tmp_products
  UNIONSELECTDISTINCT id, grp_id AS parent_id
  FROM tmp_products;

  INSERTINTO  tmp_products
  SELECT P.*, cat.id, grp.id
  FROM linked_TreeNode lc 
  JOIN treeNode tn
  JOIN product prd
    ON lc.product_id = prd.id
  JOIN treeNode cat
    ON lc.treeNode_id = cat.id
  JOIN treeNode grp
    ON lc.treeNode_id = grp.id
  JOIN tmp_parents par
    ON (par.parent_id = cat.parent_id
    OR par.parent_id = grp.parent_id)
    AND par.id <> lc.product_id
  WHERE prd.parent_id = typ;
  -- see how many rows were inserted. If this becomes zero, the recursion is completeSET@rownum= ROW_COUNT();
END WHILE;

SELECT*FROM tmp_products;
END$$

This is not tuned or tested and i would not recommend it either as it can take a long time for the query to return.

comprasconencanto1