How Laravel generates SQL queries

Question

I have created a one-to-many relationship. Here are the model classes.

class Photo extends Model
{
    public function user(){
        return $this->belongsTo('App\User');
    }
}

class User extends Authenticatable
{ 
    public function photos(){
        return $this->hasMany('App\Photo');
    }
}

Then I try to retrieve photos:

$photos = User::find(1)->photos->where('photo', 'ut.jpg')->first();

Here is a query log I got. I do not see the photo='ut.jpg'. So how laravel generate SQL?

select * from `photos` where `photos`.`user_id` = 1 and `photos`.`user_id` is not null

Possible duplicate of [Laravel where on relationship object](https://stackoverflow.com/questions/29989908/laravel-where-on-relationship-object) — Harun Yilmaz, Apr 08 '19 at 09:06

score 3 · Accepted Answer · answered Apr 08 '19 at 09:14

3

Try this

$photos = User::find(1)->photos()->where('photo', 'ut.jpg')->first();

must be use ->photos() instead of ->photos.

For see sql query use

$sql = User::find(1)->photos()->where('photo', 'ut.jpg')->toSql();

answered Apr 08 '19 at 09:14

Davit Zeynalyan

8,418
5
30
55

score 1 · Answer 2 · answered Apr 08 '19 at 09:19

You queried all photos by using this:

$photos = User::find(1)->photos->where('photo', 'ut.jpg')->first();

By using User::find(1)->photos you receive a Laravel Collection. Those collections have a where method as well. So basically, you are running SQL to get all photos of User 1 and then you just filter that collection to only show you the item with photo ut.jpg.

Instead, you can use brackets to get the relationship, and then query that. Your query then becomes

$photos = User::find(1)->photos()->where('photo', 'ut.jpg')->first();

Instead of naming it $photos you should name it $photo, as you are querying with first - which will result only in one object (or null).

score 0 · Answer 3 · answered Apr 08 '19 at 09:03

0

Can you please try this:

$photo = 'ut.jpg';

$photos = User::find(1)->whereHas('photos', function ($query) use($photo){
return $query->where('photo', $photo);
})->first();

answered Apr 08 '19 at 09:03

kapitan

2,008
3
20
26

score 0 · Answer 4 · answered Apr 08 '19 at 09:03

0

your query $photos = User::find(1)->photos->where('photo', 'ut.jpg')->first(); is incorrect, laravel didnt see the where condition if you do this

User::whereHas('photos', function($q) {
      $q->where('photo', 'ut.jpg');

})->where('id',1)->first();

thats the correct query to get the user photo

answered Apr 08 '19 at 09:03

Mohammad hayajneh

623
2
11
32

I took the example from the documentation https://laravel.com/docs/5.8/eloquent-relationships#one-to-many. $comment = App\Post::find(1)->comments()->where('title', 'foo')->first(); The result that i got is correct. But the query not. – user3351236 Apr 08 '19 at 09:14
@user3351236 $comment = App\Post::find(1)->comments()->where('title', 'foo')->first(); the where condition is on the post not the comment and the one you did was on the photo not the user – Mohammad hayajneh Apr 08 '19 at 10:42

score 0 · Answer 5 · edited Apr 08 '19 at 09:44

0

You could: Run A Select Query

$photos = DB::select('select * from photos where id = ?', [1]);

All this is well-documented in : --https://laravel.com/docs/5.0/database

edited Apr 08 '19 at 09:44

Miron

1,007
2
17
31

answered Apr 08 '19 at 09:03

benji127

345
4
10

How Laravel generates SQL queries

5 Answers5