There is an EAV base with tables: product - products (key: product_id); params - product parameters (keys: product_id, param_id); value - values ​​of product parameters (key: param_id)

Please tell me how to display in a flat table a set of parameters for products such as this:

product_id | param_id1 | param_id2 | param_id3 1 | value1 | value2 | value3 2 | value1 | value2 | value3 3 | value1 | value2 | value3
  • The number of properties depends on the type of product or can it be random for each product? - fens
  • Yes, for example: TV and shoes with their own characteristics - Monitorkin
  • Then you should have a table with a list of fields for each type of product, select all these properties accordingly and, based on the type of product, generate a request for the similarity of what Akina led to, where each Join will include a table of value for each property of the product after designing the request execute it and get every type of product with all their properties. - fens
  • Approximately it should turn out like this: habrahabr.ru/post/45935 only in your case it will be wrapped in a cycle by the type of product - fens

1 answer 1

 SELECT t0.product_id , t1.value param_id1 , t2.value param_id2 , t3.value param_id3 -- остальные параметры FROM product t0 LEFT /* or INNER */ JOIN params t01 ON t0.product_id = t01.product_id LEFT /* or INNER */ JOIN value t1 ON t01.param_id = t1.param_id LEFT /* or INNER */ JOIN params t02 ON t0.product_id = t02.product_id LEFT /* or INNER */ JOIN value t2 ON t02.param_id = t2.param_id LEFT /* or INNER */ JOIN params t03 ON t0.product_id = t03.product_id LEFT /* or INNER */ JOIN value t3 ON t03.param_id = t3.param_id -- остальные копии таблиц
  • thanks, I will try - Monitorkin