How can I concat a list of recursive self joins into a string in MySQL?

Question

I have 4 tables:

products
- id
- name

categories
- id
- name

category_products
- product_id
- category_id

category_relationships
- parent_category_id
- child_category_id

Every product has one category. Every category has [n] parent categories through the category_relationships table.

How could I write a query that results like this:

| product.id | product.name | category_list                      |
*------------*--------------*------------------------------------*
| 1          | test product | cat1, parent1, gparent1, gparent 2 |

I'm sure it's some combination of self joins, unions, etc, but I can't seem to get the right combination.

UPDATE: I'm going to not solve this with sql, and just solve it with a script that hits MySQL.

Newer versions (MySQL 8 or MariaDB 10.2) support Recursive Common Table Expressions. Without them you would need to write a stored prcedure or use some "hacks" with session variables. JOINs would only work if `[n]` has a known limit. — Paul Spiegel, Jan 06 '18 at 21:43
@PaulSpiegel I'm trying to just do an export of the data this way, does that change anything? I don't need it to be a running query. — bentedder, Jan 06 '18 at 22:00
If it's a one-time-task, then use a recursive function in a language of your choice. — Paul Spiegel, Jan 06 '18 at 22:24
Yeah, good call. No need to go crazy w/a sql script if I don't need to. — bentedder, Jan 06 '18 at 22:52

score 0 · Answer 1 · answered Jan 07 '18 at 06:31

Actually I came up with a query to do this. I already knew my recursion didn't go deeper than 3 or 4, so I played it safe at 6 levels, grouped by the concatenation of the parent categories (as some products started with multiple categories as well). This produces a result set that is the exact length as my number of products, and spits out the list of parent categories in a space-separated strings (to be used as tags).

SELECT
    products.id,
    products.name,
    category_products.category_id,
    GROUP_CONCAT( 
      CONCAT_WS(' ', 
      (select name from categories where id = c1.parent),
      (select name from categories where id = c2.parent),
      (select name from categories where id = c3.parent),
      (select name from categories where id = c4.parent),
      (select name from categories where id = c5.parent),
      (select name from categories where id = c6.parent)
    ) SEPARATOR ' ') as parents,
    (select name from categories where id = c1.parent) as c1parent,
    (select name from categories where id = c2.parent) as c2parent,
    (select name from categories where id = c3.parent) as c3parent,
    (select name from categories where id = c4.parent) as c4parent,
    (select name from categories where id = c5.parent) as c5parent,
    (select name from categories where id = c6.parent) as c6parent
  from products 
  LEFT JOIN category_products
    LEFT JOIN category_relationships as c1 on category_products.category_id = c1.child
    LEFT JOIN category_relationships as c2 on c2.child = c1.parent
    LEFT JOIN category_relationships as c3 on c3.child = c2.parent
    LEFT JOIN category_relationships as c4 on c4.child = c3.parent
    LEFT JOIN category_relationships as c5 on c5.child = c4.parent
    LEFT JOIN category_relationships as c6 on c6.child = c5.parent
  on product_id = products.id
  GROUP BY id;

How can I concat a list of recursive self joins into a string in MySQL?

1 Answers1