How to query the posts that belong to filters from an array ex: posts that belong to filter 3 and 5

user_11733323 0
Viewed 111

I am trying to figure out how to get the posts that belong to some filters given by the user. For example, the user gives some filters in this case filter with id 3 and 5. I tried eloquent queries with where Has statement but none of them worked. I am using Lumen, if you guys need the table view please let me know.

The code I am trying to get the posts with:

The problem here is that it gets all the posts that have filter_id 3 or 5: 

$post = Post::whereHas('filters', function ($q){
    $q->whereIn('filters_id',[3,5]);
    })->get();

The problem here is that it gets all the posts that in the assigneds table for filter_id have 3

$post = Post::whereHas('filters', function ($q){
    $q->where(['filters_id'=> [3,5]]);
    })->get();

Post Model: 

class Post extends Model {
public function filters(){
    return $this->belongsToMany(Filters::class, 'assigneds','post_id','filters_id');
    }

Filters Model: 

class Filters extends Model {
public function posts(){
    return $this->belongsToMany(Post::class, 'assigneds','post_id','filters_id');
    }

I have also tried writing direcly the query like this:

select * from posts where exists (select * from filters inner join assigneds on filters.id=assigneds.filters_id where posts.id= assigneds.post_id and (filters_id=3 and filters_id=5))

But it didn't work. Since I am trying to get the posts that belong to id 3 and 5 where In will not do the job, also filter id 3 and 5 are only for testing, I am trying to find a proper way to get the posts that belong te each element on the array.

Example: 

array = [3,7,8,10]
Get all the posts that have assigned filter_id 3 and 7 and 8 and 10
eloquent laravel mysql php database
2 Answers
SELECT and
     , qualify
     , only
     , the
     , columns 
     , you
     , actually 
     , want
  FROM posts p
  JOIN assigneds a
    ON a.post_id = p.id
  JOIN filters f
    ON f.id = a.filters_id 
 WHERE a.filters_id IN(3,5)
GROUP
    BY all
     , unaggregated 
     , columns
     , appearing 
     , in
     , the
     , select
     , clause
HAVING COUNT(*) = 2; -- (or COUNT(DISTINCT something))
user_15296730

Okay, so this one worked fine for me, although it takes more time than I prefer for now I can't do better, if I go back to this project in the future and I make changes I will comment in this reply.

$temp = $request->filter_id;    
$post = Post::whereDoesntHave('filters', function ($q) use ($temp){
    foreach($temp as $filter){
        $q->where('filters_id', '!=' , $filter);
    }
})->get();
user_117333230
Related