Get all products under a given collection

Question

I have a MYSQL table called collections when viewed and implemented as a table could be something like this:

I needed to know whether one mysql query will be able to get all the products under a collection type entry (a given) which could have collections under it. For example, if I select 10, it should return 14, 12, 13, and 15.

I implemented a solution that involves a do..while loop...

$concatted = 10;
$products = [];
do {
    $sql = "SELECT id, type FROM collections WHERE parent IN ($id_concatted)";
    $result = $mysqli->query($sql);

    if($result) {
        while($row = $result->fetch_object()){
            if($row->type == 'product') {
                apply_changes_to_product($row->id);
            } elseif ($row->type=='collection'){
                $collections[] = $row->id;
            }
        }
    }
    if(count($collections) > 0){
        $id_concatted = implode($collections, ",");
        $continue = true;
        $collections = [];
    } else {
        $continue = false;
    }
} while ($continue);

I think that the above code is not efficient. I think it is doable with one query but I don't know how.

UPDATE: I mark this as a duplicate of How to create a MySQL hierarchical recursive query although in that post there is NO accepted solution. I got myself this solution based on one reply there (Mysql 5.6):

SELECT id, `type` FROM (
    select  id, `type`
    from    (select * from collections
         order by parent, id) products_sorted,
        (select @pv := '10') initialisation
    where   find_in_set(parent, @pv)
    and     length(@pv := concat(@pv, ',', id))
) products
WHERE
    products.`type` = 'product'

The fiddle is http://sqlfiddle.com/#!9/ea214f/2.

Side note: You have curly quotes `‘ ’` --- `“`. Those will cause parse errors, just saying. — Funk Forty Niner, Dec 19 '18 at 17:23
Welcome. Yeah, just in case someone gets the wrong impression. — Funk Forty Niner, Dec 19 '18 at 17:27
there's no selected answer on How to create a MySQL hierarchical recursive query ... checking the other one... — barudo, Dec 19 '18 at 17:46
Mysql select recursive get all child with multiple level sounds promising unfortunately, it will include children which are of type='collection'. — barudo, Dec 19 '18 at 17:55

Sonal Borkar · Answer 1 · 2018-12-19T18:02:00.900

yes, you may need to use subquery and first fetch id where parent = selectedId and type = 'collection' and then select id where parent in the subquery id and type = 'product'

Like below:

  SELECT id, type FROM collections WHERE parent IN (select id from collections where 
  parent = $id_concatted and type = 'collection') and type = 'product'

For Multiple level, Use Recursive feature of MySql. Like below:

WITH RECURSIVE COLLECTIONS_PRODUCTS (ID, TYPE, PATH)
AS
(
SELECT ID, TYPE, CAST(ID AS CHAR(200))
FROM COLLECTIONS
WHERE PARENT IN ($id_concatted)
UNION ALL
SELECT S.ID, S.TYPE, CONCAT(M.PATH, ",", S.ID)
FROM COLLECTIONS_PRODUCTS M JOIN COLLECTIONS S ON M.ID=S.PARENT
)
SELECT * FROM COLLECTIONS_PRODUCTS WHERE TYPE = 'product' ORDER BY PATH;

that will not work if the input is 1... – barudo Dec 19 '18 at 17:30 — barudo, Dec 19 '18 at 17:30

Get all products under a given collection

1 Answers1