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
2
Answers
I think you should split your filters array into 2 separate arrays:
filters
for filtering Ads andmodelFilters
for filtering relation (bike_models).Then you can try to use
with
andwhereHas
orwithWhereHas
functions:or you can do:
The result will be the same
with()
is for eager loading. You can’t use normalwhere
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 formodel
, other foruser
, and other forimages
if your$filters
array can have filters for all relations. If you want to filter based only onmodel
relation, you can skipp this step.You have 3 options to use your array to filter:
WhereHas
Constraining Eager Loads
Constraining Eager Loads With Relationship Existence
To get a more accurate solution, please add your Laravel version to the question. This answer was based on
Laravel 9.x
.