Mysql查询来自3个表和where子句

I have 3 tables in my Database

我的数据库中有3个表

# Items
id - name 
----------
1  -  Item A 
2  -  Item B 
3  -  Item C 

.

# Traits 
id - name 
----------
1  - Color
2  - Grade
3  - Size

.

# Values  
id - item_id - trait_id - value  
----------
1  - 1  - 1  - red 
1  - 2  - 1  - green 
1  - 3  - 1  - red 
1  - 1  - 2  - 90% 
1  - 2  - 2  - 45% 
1  - 3  - 2  - 80%
1  - 1  - 3  - 4 inches
1  - 2  - 3  - 5 inches
1  - 3  - 3  - 9 inches

In Laravel, I could get all items with their traits and values using "belongsTo" [$this->belongsTo('App\Traits','trait_id');] in Value model to get the results like so :

在Laravel中,我可以使用“belongsTo”[$ this-> belongsTo('App \ Traits','trait_id');]在Value模型中获取所有具有其特征和值的项目,以获得如下结果:

--- Item A 
Color: red
Grade: 90%
Size: 4 inches

--- Item B 
Color: green
Grade: 45%
Size: 5 inches

.. etc 

from code like this :

来自这样的代码:

$items = Items::get();
foreach ($items as $item) {
  echo '<h2>'.$item->name.'</h2>';
  foreach ($item->values as $value) {
      echo '<b>'.$value->trait->name . '</b>: ';
      echo $value->value . '<br>';
  }
}

However, what I couldn't do is that I need to filter these results, for examples, I only need Items that its color is "red" and its grade bigger than 70% ?

但是,我不能做的是我需要过滤这些结果,例如,我只需要其颜色为“红色”并且其等级大于70%的项目?

If you don't use Larave, feel free to write it in pure mysql queries, I may find a way to do it in Laravel when I get the idea.. Thank you

如果您不使用Larave,请随意在纯mysql查询中编写它,当我得到这个想法时,我可能会在Laravel中找到一种方法...谢谢

1 个解决方案

#1

Filtering items/entities by (multiple) attribute-value pairs is one of the disadvantages of the EAV model. There some ways to achieve that. One is to join the Items table with Traits and Values once per condition:

通过(多个)属性 - 值对过滤项目/实体是EAV模型的缺点之一。有一些方法可以实现这一目标。一种是每个条件将Items表与Traits和Values连接一次:

select i.*
from Items i
join `Values` v1 on v1.item_id = i.id
join `Values` v2 on v2.item_id = i.id
join Traits t1 on t1.id = v1.trait_id
join Traits t2 on t2.id = v2.trait_id
where t1.name = 'Color' and v1.value = 'red'
  and t2.name = 'Grade' and v2.value > 70

sqlfiddle

You can also use pivoting to get a result with the columns (item_id, Color, Grade):

您还可以使用透视来获取列的结果(item_id,Color,Grade):

select v.item_id
  , max(case when t.name = 'Color' then v.value end) as Color
  , max(case when t.name = 'Grade' then v.value end) as Grade
from `Values` v
join Traits t on t.id = v.trait_id
group by v.item_id
having Color = 'red' and Grade > 70

This result can be joined with the Items table to get the filtered Items. A modification can also be used in a WHERE-IN condition:

此结果可以与Items表连接以获取过滤的Items。修改也可以在WHERE-IN条件中使用:

select * from Items
where id in (
  select v.item_id
  from `Values` v
  join Traits t on t.id = v.trait_id
  group by v.item_id
  having max(case when t.name = 'Color' then v.value end) = 'red'
     and max(case when t.name = 'Grade' then v.value end) > 70
);

sqlfiddle

Another approach:

select * from Items
where id in (
  select v.item_id
  from `Values` v
  join Traits t on t.id = v.trait_id
  where t.name = 'Color' and v.value = 'red'
     or t.name = 'Grade' and v.value > 70
  group by v.item_id
  having count(v.item_id) = 2
);