Multiple where clauses on multiple tables Laravel without join

Question

I am currently developping a crud application with Laravel. I have an Ad class that contains a user_id and a model_id. I want to filter my ads with user and model table fields. I managed to do the request with multiple joins, but I wonder if there is a way without joins and without the DB class. I tried to write a request with with() function but it doesn't work.

Code of the working request with joins

$ads = DB::table('ads')
        ->join('bike_models', 'ads.model_id', '=', 'bike_models.id')
        ->join('users', 'ads.user_id', '=', 'users.id')
        ->join('images', 'ads.id', '=', 'images.ad_id')
        ->select('ads.*', 'bike_models.*', 'users.*', 'images.*')
        ->where($filters)
        ->get();

Working request without where clause

$ads = Ad::with(['model', 'user', 'images'])->get();

Not working request with where clause

$ads = Ad::with(['model','user','images'])->where($filters)->get();

Thanks for your help :)

EDIT

Here is the content of the filter array

Here is the content of my database :

Ads table : id, price, km, model_id, user_id

bike_models table : id, year

Could you add the value of $filters as well? (I think you are using columns that the Ad model doesn't have) — Victor
– Victor, Commented Dec 14, 2022 at 14:01
Please simpify the question: remove un-related fields. Have you tried using Eloquent relationship models — Huy Phạm
– Huy Phạm, Commented Dec 14, 2022 at 14:58

Jean Marcos · Accepted Answer · 2022-12-14 16:05:53Z

with() is for eager loading. You can't use normal where functions to filter based on eager loaded relations because these relations are queried after the main query in finished.

To get what you want:

You need to split your $filters array by relation: 1 filter array for model, other for user, and other for images if your $filters array can have filters for all relations. If you want to filter based only on model relation, you can skipp this step.
You have 3 options to use your array to filter:

WhereHas

$ads = Ad::with(['model','user','images'])
    ->whereHas('model', function($q) use ($filters) {
        $q->where($filters);
    })
    ->get();

Look that if you want to filter by many relations with many $filters array you need to adjust this query, because whereHas works only for one relation at time.

Constraining Eager Loads

$ads = Ad::with(['user','images', 'model' => function($q) use ($filters) {
        $q->where($filters);
    }])
    ->get();

In the same way, if you want to filter by many relations with many $filters array you need to adjust this query, because you need to create a functionto filter every loaded model in with.

Constraining Eager Loads With Relationship Existence

$ads = Ad::with(['user', 'images'])
    ->withWhereHas('model', function($q) use ($filters) {
        $q->where($filters);
    })
    ->get();

To get a more accurate solution, please add your Laravel version to the question. This answer was based on Laravel 9.x.

Max Trunnikov · Accepted Answer · 2022-12-14 15:56:51Z

I think you should split your filters array into 2 separate arrays: filters for filtering Ads and modelFilters for filtering relation (bike_models).

Then you can try to use with and whereHas or withWhereHas functions:


$modelFilters = [['year', '>=', '2018'], ['year', '<=', '2022']];
$filters = [...]; // other filters: km, price

Ad::query()
  ->with(['user', 'images'])
  ->withWhereHas('model', fn ($query) => $query->where($modelFilters))
  ->where($filters)
  ->get();

or you can do:


$modelFilters = [['year', '>=', '2018'], ['year', '<=', '2022']];
$filters = [...]; // other filters: km, price

$filterModels = fn ($query) => $query->where($modelFilters)

Ad::query()
  ->with([
    'user', 
    'images', 
    'model' => $filterModels
  ])
  ->whereHas('model', $filterModels)
  ->where($filters)
  ->get();

The result will be the same

Collectives™ on Stack Overflow

Multiple where clauses on multiple tables Laravel without join

2 Answers 2

WhereHas

Constraining Eager Loads

Constraining Eager Loads With Relationship Existence

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related