Mysql Question posted in
The official MySQL documentation can be found here.

mysql breadcrumb with multiple joins

I would like to select all active categories and view it with a breadcrumb. In my database I have the following:

id_category category_name velleman_id velleman_parent prestashop_category prestashop_category_2 prestashop_category_3 prestashop_category_4 deleted
1 Car & Bike 10 0
2 Bike 10.10 10 0
3 Car 10.15 10 0
4 Electric mat. 15 0
5 Wires 15.10 15 0
6 Connectors 15.15 15 0
7 Male 15.15.10 15.15 0
8 Round 15.15.10.10 15.15.10 0

The problem with my query is that it only shows categories where velleman_id contains 4 groups (like in id_category 8).

SELECT
    IFNULL(c4.id_category, IFNULL(c3.id_category, IFNULL(c2.id_category, IFNULL(c1.id_category, '')))) AS id_category,
    IFNULL(c1.velleman_id, '') AS velleman_id,
    IFNULL(c2.velleman_id, '') AS velleman_id,
    IFNULL(c3.velleman_id, '') AS velleman_id,
    IFNULL(c4.velleman_id, '') AS velleman_id,
    CONCAT(IFNULL(c1.category_name, ''), CONCAT(' > ', c2.category_name), CONCAT(' > ', c3.category_name), CONCAT(' > ', c4.category_name)) AS category_name,
    IFNULL(c4.prestashop_category, IFNULL(c3.prestashop_category, IFNULL(c2.prestashop_category, c1.prestashop_category))) AS prestashop_category,
    IFNULL(c4.prestashop_category_2, IFNULL(c3.prestashop_category_2, IFNULL(c2.prestashop_category_2, c1.prestashop_category_2))) AS prestashop_category_2,
    IFNULL(c4.prestashop_category_3, IFNULL(c3.prestashop_category_3, IFNULL(c2.prestashop_category_3, c1.prestashop_category_3))) AS prestashop_category_3,
    IFNULL(c4.prestashop_category_4, IFNULL(c3.prestashop_category_4, IFNULL(c2.prestashop_category_4, c1.prestashop_category_4))) AS prestashop_category_4,
    IFNULL(c4.deleted, IFNULL(c3.deleted, IFNULL(c2.deleted, c1.deleted))) AS deleted
FROM
    velleman_categories c1
LEFT JOIN velleman_categories c2 ON
    c2.velleman_parent = c1.velleman_id
LEFT JOIN velleman_categories c3 ON
    c3.velleman_parent = c2.velleman_id
LEFT JOIN velleman_categories c4 ON
    c4.velleman_parent = c3.velleman_id
WHERE
    c1.velleman_parent = 0;

Tried to select with IFNULL and left join on the same table but with reference to previous joins.

When I select all active rows, I have 523 rows:

SELECT * FROM velleman_categories WHERE deleted = 0;

And with the query above I have 423 rows.
This is what I like to get:

id_category velleman_id velleman_id velleman_id velleman_id category_name prestashop_category prestashop_category_2 prestashop_category_3 prestashop_category_4
4 15 (empty or null) (empty or null) (empty or null) Electric mat. (empty) (empty) (empty) (empty)
8 15 15.15 15.15.10 15.15.10.10 Electric mat. > Connectors > Male > Round (empty) (empty) (empty) (empty)

I think the problem is with the JOIN and that some parents have no values or maybe a root category.

Tags:

2

Answers


  1. 0

    You need to modify the condition in your JOIN, add the base table (c), and change the CONCAT for the category_name.

    Here is the working query:

    SELECT
IFNULL(c4.id_category, IFNULL(c3.id_category, IFNULL(c2.id_category, IFNULL(c1.id_category, '')))) AS id_category,
IFNULL(c1.velleman_id, '') AS velleman_id,
IFNULL(c2.velleman_id, '') AS velleman_id,
IFNULL(c3.velleman_id, '') AS velleman_id,
IFNULL(c4.velleman_id, '') AS velleman_id,
CONCAT_WS(' > ', c1.category_name, c2.category_name, c3.category_name, c4.category_name) AS category_name,
IFNULL(c4.prestashop_category, IFNULL(c3.prestashop_category, IFNULL(c2.prestashop_category, c1.prestashop_category))) AS prestashop_category,
IFNULL(c4.prestashop_category_2, IFNULL(c3.prestashop_category_2, IFNULL(c2.prestashop_category_2, c1.prestashop_category_2))) AS prestashop_category_2,
IFNULL(c4.prestashop_category_3, IFNULL(c3.prestashop_category_3, IFNULL(c2.prestashop_category_3, c1.prestashop_category_3))) AS prestashop_category_3,
IFNULL(c4.prestashop_category_4, IFNULL(c3.prestashop_category_4, IFNULL(c2.prestashop_category_4, c1.prestashop_category_4))) AS prestashop_category_4,
IFNULL(c4.deleted, IFNULL(c3.deleted, IFNULL(c2.deleted, c1.deleted))) AS deleted
FROM velleman_categories c
LEFT JOIN velleman_categories c1 ON SUBSTRING_INDEX(c.velleman_id, '.', 1) = c1.velleman_id
LEFT JOIN velleman_categories c2 ON SUBSTRING_INDEX(c.velleman_id, '.', 2) = c2.velleman_id AND LENGTH(c.velleman_id) - LENGTH(REPLACE(c.velleman_id, '.', '')) > 0
LEFT JOIN velleman_categories c3 ON SUBSTRING_INDEX(c.velleman_id, '.', 3) = c3.velleman_id AND LENGTH(c.velleman_id) - LENGTH(REPLACE(c.velleman_id, '.', '')) > 1
LEFT JOIN velleman_categories c4 ON SUBSTRING_INDEX(c.velleman_id, '.', 4) = c4.velleman_id AND LENGTH(c.velleman_id) - LENGTH(REPLACE(c.velleman_id, '.', '')) > 2

    Result:
    here

    Login or Signup to reply.
  2. 0

    I am using the fact that CONCAT(null,'text') returns null, so I need not use IFNULL:

    SELECT 
   c1.id_category,
   c1.velleman_id,
   c2.velleman_id as id_2,
   c3.velleman_id as id_3,
   c4.velleman_id as id_4,
   CONCAT(COALESCE(CONCAT(c4.category_name,' > '),''),
          COALESCE(CONCAT(c3.category_name, ' > '),''), 
          COALESCE(CONCAT(c2.category_name, ' > ')), 
          c1.category_name)  as breadcrumb
FROM velleman_categories c1
LEFT JOIN velleman_categories c2 ON c2.velleman_id  = c1.velleman_parent
LEFT JOIN velleman_categories c3 ON c3.velleman_id  = c2.velleman_parent
LEFT JOIN velleman_categories c4 ON c4.velleman_id  = c3.velleman_parent
;

    output:

    id_category velleman_id id_2 id_3 id_4 breadcrumb
    1 10
    2 10.10 10 Car & Bike > Bike
    3 10.15 10 Car & Bike > Car
    4 15
    5 15.10 15 Electric mat. > Wires
    6 15.15 15 Electric mat. > Connectors
    7 15.15.10 15.15 15 Electric mat. > Connectors > Male
    8 15.15.10.10 15.15.10 15.15 15 Electric mat. > Connectors > Male > Round

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search