Level 22
@nenads Why not just $user->tests()->get() ?
Nov 4, 2017
8
Level 6
Find results for specified user if they exists
Hi to all,
I have three tables in my database: users, tests and test_user. One user can have many tests, and one test can have many users.
I want to show list of all tests (even if user didn't do any test) and results for specified user if any record exist in pivot table.
class User extends Model
{
public function tests()
{
return $this->belongsToMany(Test::class);
}
}
class Test extends Model
{
public function users()
{
return $this->belongsToMany(User::class);
}
}
class TestController extends Controller
{
public function index(User $user)
{
$tests = Test::with(['users' => function($query) use($user) {
$query->where('users.id', $user->id);
}])->get();
return $tests;
}
}
Index method in TestController works find but now a have $tests->users (plural) even if I have only one user and I don't like it.
@foreach($tests as $test)
Name: {{ $test->name }}<hr>
@foreach($test->users as $user)
Date: {{ $user->pivot->created_at }}<br>
@endforeach
@endforeach
Is there any better way to do this?
Thanks!
Level 6
@andonovn If user didn't do some test I won't get get that test name. One user can do one test zero or many times.
For example:
User: John
Test Name: Test 1 Date: 4.11.2017. Date: 5.11.2017.
Test Name: Test 2 None
Test Name: Test 3 Date: 1.11.2017.
etc.
If there is no data in pivot table, I cannot associate user with tests.
Level 37
Update the relationship to do a leftJoin. (may work)
Something like this:
public function tests()
{
$relation = $this->belongsToMany(Test::class);
$myWheres = [];
foreach($relation->getQuery()->getQuery()->wheres as $where) {
if ($where['type'] != 'Join') { // I think its 'Join'
$myWheres[] = $where;
}
}
$relation->getQuery()->getQuery()->wheres = $myWheres; //reset
$relation->getQuery()->getQuery()->leftJoin('users_tests','test_id','=','user_id');
return $relation;
}
Level 22
@nenads Then the only thing in my mind is to manually write the query. Something like this (not tested tho)
$tests = Test::leftJoin('test_user', 'test_user.test_id', '=', 'tests.id')
->leftJoin('users', 'test_user.user_id', '=', 'users.id')
->where(function ($query) {
$query->where('users.id', '=', $user->id)
->orWhereRaw('users.id IS NULL');
});
I don't really like the @robrogers3 suggestion as there would be one hell of methods to define the same relationships for different use-cases. It's more straight-forward to have the relationships defined in one place only, and use them per your needs.
Level 37
yep @andonovn would be great IF it worked with relationships.
you can create a accessor, getTestsAttribute which can return the results of the above query. Check the belongsToMany.php relationship.
Level 23
what i would do is
$tests = test::all();
$users = user::with('tests')->get()
then loop through the tests, do a check if users->test contain that & show it...
1 like
Level 6
@shez1983 That's exactly what I'm using right now but it requires two nested foreach loop, outer for the tests and inner for user's test results with if statement to check ids.
I think I'll leave it as is for now until I find some better solution.
Thanks to all!
Level 6
I've created hasMany relationship between tests and test_user and now I can do something like this:
$tests = Test::with(['results' => function($query) use ($user) {
$query->with('user_id', $user->id);
}])-get();
With this relation I don't have to check ids on inner loop, I can simply iterate over both loops and just display results.
Thanks!
Please or to participate in this conversation.