Friday, 23 August 2013

How to select items on table based on 3 relations

How to select items on table based on 3 relations

I have 3 tables:
Categories
| id | name
| 1 | Samsung
| 2 | Apple
Products
| id | category_id | name
| 1 | 1 | Galaxy S4
| 2 | 1 | Galaxy S3
| 3 | 1 | SHG-G600
| 4 | 3 | Lumia 920
Tags
| id | product_id | name | type
| 1 | 1 | smart-phone | phoneType
| 2 | 2 | smart-phone | phoneType
| 3 | 3 | normal-cell | phoneType
| 4 | 1 | red | phoneColor
I'm trying to find a way to select all Samsung devices which have
'smart-phone' as 'phoneType' and 'red' as 'phoneColor'.
So this what I did until now:
SELECT *
FROM `products`
INNER JOIN `product_tag` ON `product_tag`.`product_id` = `products`.`id`
INNER JOIN `tags` ON `tags`.`id` = `products`.`id`
WHERE (
`tags`.`type` = 'phoneType'
AND `tags`.`name` = 'smart-phone'
)
OR (
`tags`.`type` = 'phoneColor'
AND `tags`.`name` = 'red'
)
)
This did not work as is (without selecting category).
I also didn't know how to join categories and add where categories.id = 1
(I added the tags laravel-4 laravel so that laravel query builder gurus
can answer)

No comments:

Post a Comment