Support the ongoing development of Laravel.io →
posted 9 years ago
Eloquent
Last updated 1 year ago.
0
$posts = Post::whereHas('tags', function($q)
{
    $q->where('name', 'like', 'foo%')
      ->where('name', 'like', 'bar%');

})->get();

does this do the trick?

Last updated 1 year ago.
0

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?

Last updated 1 year ago.
0

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();
Last updated 1 year ago.
0

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.

Last updated 1 year ago.
0

So you want only posts that have all related tags in the array?

Last updated 1 year ago.
0

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)

Last updated 1 year ago.
0

I recommend you first to build your sql query directly on db then convert it to eloquent

Last updated 1 year ago.
0

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

Last updated 1 year ago.
0

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

Last updated 1 year ago.
0

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!

Last updated 1 year ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

vanpet vanpet Joined 10 Feb 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.