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

CodePudding user response：

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:

1. 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.

2. 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.

CodePudding user response：

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