I have database structure:
test
test_id name ...
user
user_id name ...
test_result
result_id score ...
test_assignment
user_id test_id result_id
I have easy implementation of Many-to-Many relationship between user and test with test_assignment table as link table:
User.php:
public function assignedTests(): Relation
{
return $this-\>belongsToMany(Test::class, 'test_assigment', 'user_id', 'test_id')
->withPivot('result_id');
}
Test.php:
public function users(): Relation
{
return $this->belongsToMany(User::class, 'test_assigment', 'test_id', 'user_id')
->withPivot('result_id');
}
I faced with the problem of loading the results of tests by 'result_id' in link table at the same time with loading assined tests for user.
I solved it by this way:
- Added to test model relation with result:
public function results(): Relation
{
return $this->belongsToMany(Result::class, 'test_assigment', 'test_id', 'result_id');
}
- Use -> load() for already loaded collection of assigned tests with filtering pivot value ('user_id'):
$tests = auth()->user()->assignedTests()->orderBy('result_id', 'asc')->get();
$tests->load(['results' => function (BelongsToMany $belongsToMany) {
$belongsToMany->wherePivot('user_id', auth()->user()->getAuthIdentifier());
}]);
Then i use $tests[1]->results->first() to get the result of test (->first() because by default belongsToMany retrieve collection (bad)).
It doesn't seem like the best solution and i want to know if there is any proper way to solve this problem.