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)
->orderBy('title','ASC')
->paginate(20);
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)
->orderBy('title','ASC')
->paginate(20);
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
3 of 5 based on 1119 votesComments