How to make this eav request to get horizontal result

One approach is to use correlated subqueries in the SELECT list, although this may be less optimal for performance on large sets. For getting just a few rows of rows from the product table, this would not be the case. (You will definitely need the appropriate indexes.)

SELECT p.product_id
     , ( SELECT a1.value
           FROM attribute a1
           JOIN product_attribute q1
             ON q1.attribute_id = a1.attribute_id
          WHERE q1.product_id = p.product_id
            AND a1.attribute_name = 'height'
          ORDER BY a1.id
          LIMIT 0,1
       ) AS height_1
     , ( SELECT a2.value
           FROM attribute a2
           JOIN product_attribute q2
             ON q2.attribute_id = a2.attribute_id
          WHERE q2.product_id = p.product_id
            AND a2.attribute_name = 'width'
          ORDER BY a2.id
          LIMIT 0,1
       ) AS width_1
  FROM product p
 WHERE p.product_id = 1

      

        
        
        
      

    

This query will return a string from the product along with attribute values, if any. If no attribute values ​​are found, the query returns NULL instead of the attribute value. (This differs from the behavior of a query that uses an INNER JOIN instead of a correlated subquery ... where a "missing" row from the attribute table or product_attribute filters the row from the returned product.)

The purpose of LIMIT clauses is to ensure that subqueries return rather than returning more than one row. (If more than one row was returned in a subquery in the SELECT list, MySQL will return an error.) The purpose of ORDER BY is to make the query deterministic, again, in case there is more than one row that satisfies the subquery, (In the absence of an ORDER BY clause, when there is more than one row, MySQL can randomly return whichever row it chooses.)

The same approach works for "multi-valued" attributes. We just add more subqueries, but we specify LIMIT 1.1 to return the second attribute value, LIMIT 2.1 to return the third value, and so on.

(Oh, the joy of the EAV model implemented in a relational database.)

Followup:

Q: "... a more general case, as it happens in eav db, that we don't know before what attribute names we have."

A: The relational model is based on the principle that a tuple contains a specified number of columns of a specified type.

What you are (apparently) trying to do is return a variable number of columns when you run your query. The SELECT statement contains a specific list of returned expressions; it cannot change, and the data types returned by each expression do not change from row to row.

The query above returns one instance of the "height" attribute value and one instance of the value of the "width" attribute for each product.

For the more "more general case", we really expect each attribute value to be returned on its own separate line.

A more general query, if you don't know "ahead of time" which attributes are associated with a product, would be:

SELECT p.product_id
     , a.attribute_id
     , a.name         AS attribute_name
     , a.value        AS attribute_value
  FROM product p
  LEFT
  JOIN product_attribute q
    ON q.product_id = p.product_id
  LEFT
  JOIN attribute a
    ON a.attribute_id = q.attribute_id
 WHERE p.product_id = 1
 ORDER
    BY p.product_id
     , a.name
     , a.attribute_id

      

        
        
        
      

    

This will return a result set that can be easily processed:

product_id attribute_id attribute_name attribute_value
---------- ------------ -------------- ---------------
         1            6 height         380
         1            2 width          320

      

        
        
        
      

    

Q: "looks like it should be done in 2 steps: 1. get all the attribute names for the product 2. then your code with the server side code of the attribute names in a loop"

A: No, it looks like one query is returning all attribute name and value pairs for a product. Each attribute name / value will be on a separate line.

There is no need to use a for loop to generate additional database queries. Yes, it can be done, but completely unnecessary.

If you have some weird requirement to compose another database query to return a result set in the format you specify, regardless of the processing you do to process the result set from the "more general case" operator, it probably would be process the result set more efficiently without running any more database queries.

If you need to return a result set that looks like this:

 product_id height width
 ---------- ------ -----
          1 380    320

      

        
        
        
      

    

(as a fancy requirement, since it is necessary to compose another query), it is entirely possible to use this result set from a "more general query" to generate a query that looks like this:

SELECT 1 AS product_id, '380' AS height, '320' AS width

      

        
        
        
      

    

While such an exercise is pretty pointless, given that you are not returning any new information that you have not previously returned, and now you have another set of results that you need to process, which I think is a boatload of unnecessary overhead.