John Davidson

php - Laravel whereRelation() not filtring the query


I need to select all offers where the merchant has status open and dd_merchant set to 1. The match between merchant and offer is done using a col named mid.

In my offer model I have the following:

public function merchants()
return $this->belongsTo(Merchant::class,'mid','mid');

My query looks like this:

 $data['offers']= Offer::whereRelation('merchants', 'status', 'Open')
->whereRelation('merchants', 'dd_merchant', '1')
->where('status', '=', 'Loaded')
->where("start", "<=", date("Y-m-d"))
->where("end", ">=", date("Y-m-d"))
->where('category', '=', $request->category)

For the sake of this test, I have one merchant set to status 'Closed' and has dd_merchant set to 0 in the database.

The query returned by this is

select * from `offers` where (exists (select * from `merchants` where `offers`.`mid` = `merchants`.`mid` and `status` = 'Open' and `merchants`.`deleted_at` is null) and exists (select * from `merchants` where `offers`.`mid` = `merchants`.`mid` and `dd_merchant` = '1' and `merchants`.`deleted_at` is null) and `status` = 'Loaded' and `start` <= '2022-10-03' and `end` >= '2022-10-03' ) and `offers`.`deleted_at` is null order by `title` asc limit 20 offset 0

For some reason, I am still getting offers from that merchant with the above query. Am I assuming wrong that whereRelation() filters the offers based on the condition on the second table? If so, how can I achieve my goal?

In one of the 27 variants of this I tried so fare, I used the wherehas() like this:

            $data['offers']= Offer::
whereHas('merchants', function (Builder $query) {
$query->where('dd_merchant', '=', '1')->where('status', '=', 'Open');
->where('status', '=', 'Loaded')
->where("start", "<=", date("Y-m-d"))
->where("end", ">=", date("Y-m-d"))
->where('category', '=', $request->category)

The above made a lot more sense to me but I get the same result ... I get offers that should not be there.

My Answer:
>belongsTo(Merchant::class, 'mid', 'id');_}_And in my controller, I am trying to filter the offers using the whereRelation() method as follows:_$offers = Offer::whereRelation('merchants', function ($query) {_ $query->where('status', 'open')->where('dd_merchant', 1);_})->get();_However, this query is not filtering the offers as expected. It is returning all offers regardless of the merchant's status and dd_merchant value. What am I doing wrong?

Rate this post

5 of 5 based on 4958 votes


© 2024 - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us