[SOLVED] Laravel DB query involving join and where

Laravel DB query involving join and where

I have a question about Laravel queries. I have a upload Model and database table which stores all my images. Then there is an Activity Model and database table which stores all my activities. For each activity I want a image.

So my Activity model has a 'uploads_id' column. I wrote the query like this:

$activity_images =  DB::table('uploads')
->join('activities', 'uploads.id', '=', 'activities.upload_id')
->where('uploads.id', '=', 'activities.upload_id')
->get();

It cannot find the right image. What am I doing wrong?

Solution

You are "joining" twice in your join and where clause. You connect uploads.id and activities.upload_id one time in the join and again in the where clause.

If you want to query for a special upload.id, your query should look like this:

$activity_images =  DB::table('uploads')
->join('activities', 'uploads.id', '=', 'activities.upload_id')
->where('uploads.id', '=', '<yourUploadID>')
->get();

If you want all images, you can delete the where statement.

$activity_images =  DB::table('uploads')
->join('activities', 'uploads.id', '=', 'activities.upload_id')
->get();

If you use join an inner join will be used. You could also use leftJoin if you want all entries from the table uploads.

$activity_images =  DB::table('uploads')
->leftJoin('activities', 'uploads.id', '=', 'activities.upload_id')
->get();

Does it solve your issue?