Retrieve single row as a query result for an EAV model in CodeIgniter – Phpmyadmin

I would just execute three simple queries. Get products. Get attributes. Get dimensions.
Then map attributes and dimensions to corresponding products.
This is how probably any ORM is performing eager loading.

I’m not familiar with codeigniter, but I think it should look something like this:

$query = $this->db->get('products');

$products = [];
foreach ($query->result() as $row) {
    // init empty arrays for attributes & dimensions
    $row->attributes = [];
    $row->dimensions = [];
    // should be indexed by id, so we can find id later to map attributes & dimensions
    $products[$row->id] = $row;
}

$attributes = $this->db
    ->select('pa.product_id, a.name')
    ->from('product_attributes as pa')
    ->join('attributes as a', 'a.id = pa.attribute_id')
    ->get();

$dimensions = $this->db
    ->select('pd.product_id, d.name, d.value')
    ->from('product_dimensions as pd')
    ->join('dimensions as d', 'd.id = pd.dimension_id')
    ->get();

foreach ($attributes as $attr) {
    $products[$attr->product_id]->attributes[] = $attr->name;
}

foreach ($dimensions as $dim) {
    $products[$dim->product_id]->dimensions[] = $dim;
    unset($dim->product_id);
}

Now you get all your data in a nicely nested structure. With echo json_encode($products, JSON_PRETTY_PRINT) you would get:

{
    "1": {
        "id": 1,
        "name": "Product_1",
        "description": "Lorem",
        "attributes": [
            "Attribute_1",
            "Attribute_2"
        ],
        "dimensions": [
            {
                "name": "length",
                "value": "15"
            },
            {
                "name": "breadth",
                "value": "25"
            },
            {
                "name": "height",
                "value": "15"
            }
        ]
    },
    "2": {
        "id": 2,
        "name": "Product_2",
        "description": "Ipsum",
        "attributes": [
            "Attribute_1",
            "Attribute_2",
            "Attribute_3"
        ],
        "dimensions": [
            {
                "name": "length",
                "value": "15"
            },
            {
                "name": "breadth",
                "value": "25"
            }
        ]
    }
}

You can traverse it in the view layer or your export code, and output in any way you need.

Update

In MySQL 5.7+ you can get the same result as a single JSON string with a single query:

select json_arrayagg(json_object(
  'id', p.id,
  'name', p.name,
  'description', p.description,
  'attributes', (
    select json_arrayagg(a.name)
    from product_attributes pa
    join attributes a on a.id = pa.attribute_id
    where pa.product_id = p.id
  ),
  'dimensions', ( 
    select json_arrayagg(json_object(
      'name',  d.name,
      'value', d.value
    ))
    from product_dimensions pd
    join dimensions d on d.id = pd.dimension_id
    where pd.product_id = p.id
  )
))
from products p;

db-fiddle

Or maybe you want something “simple” like this:

select p.*, (
    select group_concat(a.name order by a.id separator ', ')
    from product_attributes pa
    join attributes a on a.id = pa.attribute_id
    where pa.product_id = p.id
  ) as attributes, (
    select group_concat(d.name, ': ', d.value order by d.id separator ', ')
    from product_dimensions pd
    join dimensions d on d.id = pd.dimension_id
    where pd.product_id = p.id
  ) as dimensions
from products p;

which will return:

| id  | name      | description | attributes                            | dimensions                          |
| --- | --------- | ----------- | ------------------------------------- | ----------------------------------- |
| 1   | Product_1 | Lorem       | Attribute_1, Attribute_2              | length: 15, breadth: 25, height: 15 |
| 2   | Product_2 | Ipsum       | Attribute_1, Attribute_2, Attribute_3 | length: 15, breadth: 25             |

db-fiddle

But if you need the result exactly as in the question, then you can try this one:

select 
  p.name as product_name,
  (
    select concat('(', group_concat(a.name order by a.id separator ', '), ')')
    from product_attributes pa
    join attributes a on a.id = pa.attribute_id
    where pa.product_id = p.id
  ) as attribute_name,
  concat('(', group_concat(d.name order by d.id separator ', '), ')') as dimension_name,
  concat('(', group_concat(d.value order by d.id separator ', '), ')') as dimension_value,
  p.description as product_description
from products p
left join product_dimensions pd on pd.product_id = p.id
left join dimensions d on d.id = pd.dimension_id
group by p.id;

Result:

| product_name | product_description | attribute_name                          | dimension_name            | dimension_value |
| ------------ | ------------------- | --------------------------------------- | ------------------------- | --------------- |
| Product_1    | Lorem               | (Attribute_1, Attribute_2)              | (length, breadth, height) | (15, 25, 15)    |
| Product_2    | Ipsum               | (Attribute_1, Attribute_2, Attribute_3) | (length, breadth)         | (15, 25)        |

db-fiddle