没有连接的EAV表选择 [英] EAV table select without joins

问题描述

我目前有以下EAV表:

I currently have the following EAV table:

id
field_name
field_value

和一个标准表:

id
first_name
last_name

我将每个与ID匹配的值的标准表连接到EAV表上，因此我的查询如下所示:

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');

直到今天，我的EAV表中已有62个自定义字段，这一直很好，这意味着我的查询正在连接到62个表上，因此达到了MySQL表的连接限制并失败了.

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.

整个查询似乎是一种不好的方法，我该如何重写它，以便它更快并且不需要62个表联接.

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.

推荐答案

您还可以将聚合用于EAV.查询如下:

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.

这篇关于没有连接的EAV表选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！