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

Popular posts from this blog

css - Which browser returns the correct result for getBoundingClientRect of an SVG element? -

gcc - Calling fftR4() in c from assembly -

Function that returns a formatted array in VBA -