Mysql query from 3 tables and where clause

Question

I have 3 tables in my Database

# 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 :

--- 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% ?

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

id serves no purpose in the values table (which is, btw, a poor choice of name). A good tip, if using an EAV model, is to construct separate tables for each data type. — Strawberry
– Strawberry, Commented Jun 13, 2016 at 8:14
@Strawberry Actually it's first time I know about EAV model, all structures above came from my head without knowing anything about EAV, I'll read more about it and see what's best practice for such an approach, thanks for the tip — Ahmad
– Ahmad, Commented Jun 13, 2016 at 14:47

Paul Spiegel · Accepted Answer · 2016-06-13 00:01:13Z

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:

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):

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:

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

answered Jun 13, 2016 at 0:01

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmad Over a year ago

Thank you very much Paul! First time I know about the EAV model, I'll read more about it.

Collectives™ on Stack Overflow

Mysql query from 3 tables and where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related