Level 52
When you use eager loading like this you get ALL users and only images that pass the constraint. Your constraint for image is that user_id == 'image_id'. So I think you get no images.
Mar 15, 2015
12
Level 21
[L5] Weirdness in Querying Many to Many relation
Well, this is strange.
I have User and Images models with many to many relation. And now when I run this
User::with(['images' => function($query){
$query->where('user_id', 'image_id');
}])->get();
I get all the users.
When I saw the query with the help of Laravel" target="_blank">https://github.com/barryvdh/laravel-debugbar'>Laravel Debugbar
I found that it was checking the 'user_id' column with the (string) 'image_id'.
so technically I should not get any result as query fails coz i dont have 'image_id' string in my user_id column.
But I get all the users. (Strange).
And when i run the build query in phpmyadmin i get no result as expected.
This is the build query I got from Laravel Debugbar
select `images`.*, `image_user`.`user_id` as `pivot_user_id`, `image_user`.`image_id` as `pivot_image_id` from `images` inner join `image_user` on `images`.`id` = `image_user`.`image_id` where `image_user`.`user_id` in ('1', '7', '8', '9', '10', '11') and `user_id` = 'image_id'
Ok, now I fix the query like this.
User::with(['images' => function($query){
$query->where('user_id', '`image_id`');
}])->get();
And the build query for this is
select `images`.*, `image_user`.`user_id` as `pivot_user_id`, `image_user`.`image_id` as `pivot_image_id` from `images` inner join `image_user` on `images`.`id` = `image_user`.`image_id` where `image_user`.`user_id` in ('1', '7', '8', '9', '10', '11') and `user_id` = '`image_id`'
Which is perfect. And when i run it in phpmyadmin i get only users I requested for.
But in Laravel, Again it gives me all the users. (Strange again).
This is little weird to me. Does anyone know why is it happening? Any clue?
Level 20
If you have related models, there's no need for building the query that way, just
User::with('images')-get();
This will eager load all related images of all users
Level 21
Level 52
@SachinAgarwal so you want do the query on the pivot table where you want user_id == image_id ?
Level 21
@bestmomo exactly. But should return the Users.
Level 52
Your query cant work like that with Eloquent.
Not sure it works :
$users = User::join('image_user', function($join)
{
$join->on('user.id', '=', 'image_user.user_id')->on('user.id', '=', 'image_user.image_id');
})->get();
1 like
Level 21
@bestmomo Yes I can do like this, And it will work (tested), But I was trying to do it with relationship and eager loading. Not sure if I misunderstood the concept of eager loading or it has this BUG.
Level 52
With eager loading there are 2 queries so you cant have this kind of constraint.
1 like
Level 21
@bestmomo Thanks a lot :)
I ditn notice 2 queries before.
And so it turned out that I misunderstood the concept.
Thanks a lot, was trying to figure out this from 2 days. :P
Level 52
Doesn't work with eager loading but could work with whereHas and a raw where :
$users = User::whereHas('images', function($q)
{
$q->whereRaw('users.id = images.id');
})->get();
Level 53
@SachinAgarwal It is not strange, eager loading is not for limiting the main query, but for limiting the eager loaded relation. What you need is whereHas to limit users by images.
However, I have no idea what you tried to accomplish with where('user_id', 'image_id') - it makes no sense to me. So describe your goal.
Level 21
@JarekTkaczyk It makes no sense with 'users' and 'images'.But these are dummy tables. It just testing if I could get the users who's ID matches the image ID
@bestmomo I guess I missed the whereHas() part in documentation. Thanx this perfect. This is exactly what i was looking for. Thanks :D
Please or to participate in this conversation.