CODEWITHSUNDEEP
phpmysqlhierarchical-data
Jay
Jay

Reputation: 704

SQL query with nested/hierarchial relationship table

I have these two tables:

Table "items":
╔════╦══════════════╦═══════╗
║ ID ║     NAME     ║ TYPE  ║
╠════╬══════════════╬═══════╣
║  1 ║ Home         ║ House ║
║  2 ║ Livingroom   ║ Room  ║
║  3 ║ Kitchen      ║ Room  ║
║  4 ║ Pots         ║ Item  ║
║  5 ║ Pans         ║ Item  ║
║  6 ║ Table        ║ Item  ║
║  7 ║ Away         ║ House ║
╚════╩══════════════╩═══════╝

Table "relationships":
╔════╦═══════════╦═══════════╗
║ ID ║  ITEM_ID  ║ PARENT_ID ║
╠════╬═══════════╬═══════════╣
║  1 ║     2     ║     1     ║
║  2 ║     3     ║     1     ║
║  3 ║     4     ║     3     ║
║  4 ║     5     ║     3     ║
║  5 ║     6     ║     2     ║
╚════╩═══════════╩═══════════╝

And I am building the following array in php:

$arr = array( 
  '1' => array(
    'name' => 'Home',
    'rooms' => array(
      '2' => array(
        'name' => 'livingroom',
        'items' => array(
          '6' => 'Table'
        )
      ),
      '3' => array(
        'name' => 'kitchen',
        'items' => array(
          '4' => 'Pots',
          '5' => 'Pans'
        )
      ),
    ),
  );
  '7' => array(
    'name' => 'Away',
    'rooms' => array(
    ),
  );
);

Currently the way this is done is by first doing a query on the items table for the houses: 

SELECT
  id,
  name
FROM items
WHERE type = "House"';

Then a query on the relationships table that left joins the items table on item_id to get the name and the type.

SELECT
  i.id        AS id,
  i.name      AS name,
  i.type      AS type,
  r.parent_id AS parent
FROM relationships r
LEFT JOIN items i
ON r.element_id = i.id';

And finally three foreach loops are used to build the array. One to structure the houses from the first query. then another to separate rooms and items, and a third to combine it all into the final array.

While it works I'm sure a better solution is possible for the query, such as combining the two into one for example. How could this query be combined into one and the array be built with as few loops as possible?

Upvotes: 1

Views: 321

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

If you only have 3 nesting levels, this query will work to return the parents and their children:

SELECT 
    a.`ID`,
    a.`NAME`,
    a.`TYPE`,
    c.`NAME`,
    c.`TYPE`,
    e.`NAME`,
    e.`TYPE`    
FROM `items` a
JOIN `relationships` b
ON a.`ID` = b.`ITEM_ID`
JOIN `items` c
ON b.`PARENT_ID` = c.`ID`
JOIN `relationships` d
ON b.`PARENT_ID` = d.`ITEM_ID`
JOIN `items` e
ON d.`PARENT_ID` = e.`ID`;

Upvotes: 1

Related Questions