Mysql: Get all results that has all this relations
I have two tables:
objects object_features
------------- -------------------
id id
name object_id
term_id
What I want to achieve is, giving a list of features, get all objects that ha开发者_JS百科s all of them. I'm trying this:
SELECT objects.*
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')
This is the php code I'm using:
$feature_list = array(1,3,4,10);
$sql = 'SELECT objects.*
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('.implode(',', $feature_list).')';
This is near to what I need, but differing that it returns me any object that has any of the features given, instead of ALL the features
one option is to group by the data you want returned from object and add a having clause that counts object.id and tests to see if it is the same as the length of the array.
SELECT objects.id, objects.name
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')
group by objects.id,objects.name
having count(objects.id) = 4
Cant swear to the syntax on that as I've been writing tsql recently and don't have an instance of mysql to test on.
try
'WHERE term_id = '.impode(' AND termid = ', $features_ids).')'
This will result in:
WHERE termid = 1 AND termid = 3 AND termid = 5
Actually you need a GROUP BY to group by each object and using a HAVING clause to allow only rows that have all the termids
SELECT objects.*
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')
GROUP BY objects.id, objects.name
HAVING count(term_id) = 4
The SQL way of doing it would be:
SELECT objects.*
FROM objects
WHERE null not in
(
select of.object_id
from features f
left join object_features of on (f.id = of.id)
)
Assuming you have a features
table with all the features.
If you need to list only certain features, you can do (check out the where condition on the subquery):
SELECT objects.*
FROM objects
WHERE null not in
(
select of.object_id
from features f
left join object_features of on (f.id = of.id)
where f.id in (1,2,3,4,5)
)
精彩评论