$posts = Post::whereHas('tags', function($q)
{
$q->where('name', 'like', 'foo%')
->where('name', 'like', 'bar%');
})->get();
does this do the trick?
Michele > it does the trick only if you know in advance how many tags you have to compare. I don't. Can I do a loop inside a query? How would you chain wheres in a loop?
Does it? It checks tags like so:
... WHERE post_id=$id and name LIKE 'foo%' and name LIKE 'bar%' ...
so basically it won't do the trick.
There is no way to do this with simple relations, or am I wrong..?
edit: Well in fact I was wrong ;)
This should do the trick for you:
Post::whereHas('tags', function($q) {
$q->whereIn('tags.id', $arrayOfTags);
}, count($arrayOfTags) )->get();
I asked the wrong question, because thinkg back about my problem, it's slightly different and yields different results:
I still have many Posts linked to many Tags.
I still use an array of Tags to search Posts.
But the searching question is different:
Get all Posts where every related Tags is in the Array.
The search Array can be larger, but all Tags associated with the Post must at least be in it once.
So you want only posts that have all related tags in the array?
Yes (but the array can contain more, unrelated, Tags).
Example:
The Post "Laravel Rocks!" has the Tags "Programming, Framework, PHP"
If my search array is ("Programming, Rants, Webdev, Framework, PHP, Unrelated"), it will find my Post.
If my search array is ("Programming, Webdev"), it will NOT find my post (because "PHP" and "Framework" are not in it)
I recommend you first to build your sql query directly on db then convert it to eloquent
Diego > I'm simply don't know yet how to do it, even in "simple" SQL.
I think I can achieve it easily with Collections, directly in PHP, but the performance hit would be enormous (it's a search done on thousands of records).
vanpet this will work, though test it's performance:
$searchArray = [...]; // sanitized
$searchCount = count($searchArray);
Post::join(
DB::raw('(SELECT post_id, COUNT(tag_id) as tagsCount FROM post_tag GROUP BY post_id) AS pt'),
'posts.id', '=', 'pt.post_id')
->whereHas('tags', function($q) {
$q->whereIn('tags.id', $searchArray);
}, '=', DB::raw("CASE WHEN tagsCount < $searchCount THEN tagsCount ELSE $searchCount END") )
->get();
Now some SQL ninja please tell how to optimize it
Wow! I have to test this, and I will report back! Thx!
Ok: a few hours later, I have a semi-working solution. Thanks for your insight, you guys rock!
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast