John Davidson

Message:

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?