Level 63
I have tried this but it doesn't work as I need, I also get the results of all users and not only the current user mapped through the query.
$users = User::with(['tests' => function ($query) {
$query->with('results');
}])->get();
Apr 16, 2023
7
Level 63
Retrieve hasMany through hasMany
Hello,
I need to write a query and I search an easy way to do it with Eloquent, but it's quite difficult.
I need to retrieve all the tests and the results of these tests for all users. I will get a collection of users and for each one I will have the tests to which he has participated and for each tests his own results.
$users = User::with([
'tests',
'tests.results'
])->get();
The problem is that each test has much more results than only this user's results.
With this query, it retrieves all results from all users for the current test and not only the results binded to the user.
Can you help me ?
Thanks a lot ;).
V
Level 75
You will need to add a user_id column to the results table.
1 like
Level 63
@MichalOravec That's already the case, I have the user_id column on the results table.
Level 75
@vincent15000 Then this might work
$users = User::with(['tests.results' => function ($query) {
$query->whereColumn('tests.user_id', 'results.user_id');
}])->get();
1 like
Level 63
@MichalOravec The problem is that a test has no user_id column, a test has many users and a user has many tests, so the relationship is defined in a pivot table.
Level 75
@vincent15000 But the pivot table has user_id column.
$users = User::with(['tests.results' => function ($query) {
$query->whereColumn('test_user.user_id', 'results.user_id');
}])->get();
1 like
Level 63
@MichalOravec That's exactly what I have tested but I get an error saying that there is no user_id column in the test_user table. But sure this column is in the table. The pivot table is missing in the MariaDB query. I have also tested with adding a jointure, but it doesn't work.
Perhaps the better way is to write a query with jointures instead of using the query builder.
Please or to participate in this conversation.