EAV table select without joins

Question

I currently have the following EAV table:

id
field_name
field_value

And a standard table:

id
first_name
last_name

I am joining the standard table onto the EAV table for each value that matches the ID, so my query looks something like this:

SELECT id, first_name, last_name, fieldname1, fieldname2
FROM standard_table
LEFT JOIN EAV AS fieldname1 ON 
    (fieldname1.id = standard_table.id AND fieldname1.field_name = 'fieldname1')
LEFT JOIN EAV AS fieldname2 ON 
    (fieldname2.id = standard_table.id AND fieldname2.field_name = 'fieldname2');

This has been working fine, up until today where I now have 62 custom fields in my EAV table, this means that my query is joining onto 62 tables and so hitting the MySQL table join limit and failing.

The whole query seems like a bad way of doing it, how can I rewrite this so it is quicker and doesn't require 62 table joins.

[How to pivot a MySQL entity attribute value schema](http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema) — Mark Baker, Jul 10 '15 at 10:36
if you shows what output you required then can get better help. — Zafar Malik, Jul 10 '15 at 10:43

score 1 · Answer 1 · answered Jul 10 '15 at 10:36

1

You can also use aggregation for EAV. The query looks like:

SELECT st.id, st.first_name, st.last_name,
       MAX(CASE WHEN EAV.field_name = 'fieldname1' THEN fieldname1 END), 
       MAX(CASE WHEN EAV.field_name = 'fieldname2' THEN fieldname2 END)
FROM standard_table st JOIN
     EAV
     ON EAV.id = st.id
GROUP BY st.id, st.first_name, st.last_name;

As you get more and more columns, this can perform better than dozens of joins.

answered Jul 10 '15 at 10:36

Gordon Linoff

1,242,037
58
646
786

May be @Mike Oram need multiple rows for single id,first_name,last_name while you query will provide only max value. – Zafar Malik Jul 10 '15 at 10:44
I think this will do it. the relationship is only ever 1 row in standard_table which joins to 1 of each field names in the EAV so this should suffice, will test it now – Mike Oram Jul 10 '15 at 12:28
@ZafarMalik . . . If you prefer, you can use `GROUP_CONCAT()` instead of `MAX()`. EAV models would typically have one value per field name, except for certain well known fields. – Gordon Linoff Jul 11 '15 at 11:19

score 0 · Answer 2 · answered Jul 13 '15 at 07:59

0

The other provided answer was the inspiration for this however I below is the query I actually used:

SELECT st.id, st.first_name, st.last_name,
   (CASE WHEN `EAV`.`field_name` = 'fieldname1' THEN `EAV`.`field_value` END) AS 'fieldname1', 
   (CASE WHEN `EAV`.`field_name` = 'fieldname2' THEN `EAV`.`field_value` END) AS 'fieldname2', 
FROM standard_table st JOIN
 EAV
 ON EAV.id = st.id
GROUP BY st.id;

answered Jul 13 '15 at 07:59

Mike Oram

765
8
21

I found this actually caused a problem where the alias is overriding the value after it has been found – Mike Oram Jul 13 '15 at 13:39

EAV table select without joins

2 Answers2

Linked