Search code examples
mysqlentity-attribute-value

How to query multiple rows from EAV table design?

I have a situation where I have to use EAV table design.

I have the following two tables.

Nodes

id    name    structure_id
 1    name 1       7
 2    name 2       7

Attributes

id    node_id    name       value    structure_id
1       1        firstname  test         7
2       1        lastname   test         7
3       2        firstname  test         7

I have the following query

SELECT n.*, GROUP_CONCAT( CONCAT_WS('||', a.name, a.value) ORDER BY a.name SEPARATOR ';;' ) as _attributes
            FROM nodes n JOIN attributes a ON n.structure_id = a.structure_id where n.structure_id = 7

The above query outputs the following (only ONE row)

id: 1
name: name 1
structure_id: 7
_attributes: firstname||test;;firstname||test;;firstname||test;;firstname||test;;lastname||test;;lastname||test

How do I make it to output two rows from nodes table with their rows from attributes?

Solution

  • You will obtain desired result set if join nodes and attributes tables by structure_id and node_id. Desired result set: "from nodes table with their rows from attributes" .

    Good luck