performance - How to retrieve MySQL EAV results like a relational table -
i need extract results 1 table organized eav-feed repository. what need return results relational table. i'm using schema that:
table schema
meta table ----------------------- idmeta | entity_identity(fk) | products_idproduct(fk) | products_feeds_idfeed(fk) | value | 1 | 1 | 1 | 1 | first product first val | 2 | 2 | 1 | 1 | first product second val| 3 | 1 | 2 | 1 | 2nd product first val | 4 | 2 | 2 | 1 | 2nd product second val | 5 | 1 | 3 | 1 | 3rd product first val | 6 | 2 | 3 | 1 | 3rd product second val |
so i'm trying extract values products each idfeed. in case i'm trying somethig this:
wanted results..
+----------------------+---------------------------+---------------------------+ | products_idproduct | field1 | field2 | +--------------------+--------------------------+------------------------------+ | 1 | first product first val | first product second val | +--------------------+--------------------------+------------------------------+ | 2 | 2nd product first val | 2nd product second val | +--------------------+--------------------------+------------------------------+ | 3 | 3rd product first val | 3rd product second val | +--------------------+--------------------------+------------------------------+
i've been trying coalescing sentences i'm getting only 1 row because max function, or null values instead..:
what i'm trying now..
select distinct products_idproduct , max( if(entity_identity = 1, value, null) ) 'field1' , max( if(entity_identity = 2, value, null) ) 'field2' meta products_feeds_idfeed = 1;
and obsiously returns last line (last product)..
+----------------------+---------------------------+---------------------------+ | products_idproduct | field1 | field2 | +--------------------+--------------------------+------------------------------+ | 3 | 3rd product first val | 3rd product second val | +--------------------+--------------------------+------------------------------+
any ideas how can product results relational table?
some people advise me joins needed here... sentence i'm using (need performance stats:
select prod1.products_idproduct , prod1.value 'field1' , prod2.value 'field2' , prod3.value 'field3' , prod4.value 'field4' meta prod1 left join meta prod2 on ( prod1.products_feeds_idfeed = prod2.products_feeds_idfeed , prod1.products_idproduct = prod2.products_idproduct) left join meta prod3 on ( prod1.products_feeds_idfeed = prod3.products_feeds_idfeed , prod1.products_idproduct = prod3.products_idproduct) left join meta prod4 on ( prod1.products_feeds_idfeed = prod4.products_feeds_idfeed , prod1.products_idproduct = prod4.products_idproduct) prod1.products_feeds_idfeed = 1 , prod1.entity_identity = 1 , prod2.entity_identity = 2 , prod3.entity_identity = 3 , prod3.entity_identity = 4;
Comments
Post a Comment