What sis the correct query to filter data ?

I am trying to filter projects with active listings only. IN the filter I have a checkbox , if I need to filter projects based on the number of active listings.

My controller query is :

         $projects = projects::with('builder','city','locality')
                                    ->where('project_city', $city)
                                    ->withCount('reviews')
                                    ->orderByDesc('reviews_count')
                                    ->paginate(10)
                                    ->withQueryString();;

Model

   public function listings(){
        return $this->hasMany(Listings::class, 'project_id', 'id');
    }

The listings table has a field named 'dealstatus' which should have 'open' as value if it's an active listing.

What will be my query to display correct projects ?

Sinnbeck

4 years ago

Best Answer

Level 102

So if the checkbox is checked you want to add a whereHas on listings?

$projects = projects::with('builder','city','locality')
                                    ->where('project_city', $city)
->when($checked, function($q) {
   $q->whereHas('listings', function ($q) {
       $q->where('dealstatus', 'open');
   }) ;
})
                                    ->withCount('reviews')
                                    ->orderByDesc('reviews_count')
                                    ->paginate(10)
                                    ->withQueryString();

1 like

FounderStartup

4 years ago

Level 5

@Sinnbeck I am using if else for checked box. But I am unable to write the correct query for projects who have active listings.

Sinnbeck

4 years ago

Level 102

@FounderStartup but I just wrote it? What about the query doesn't work?

When = check if checkbox is checked (assuming you have it set in a variable above)

WhereHas = filter

Edit: i have added it to your original query