Laravel eagerly loading multiple models that are all in the same table-CodePudding

For an example
Let's say we have an users table and a posts table.

Users table has id and name columns.

Posts table has creator_id and approved_by_id that both exist (reference) in users table.
How can I eagerly load this with one query?

select * from posts where id in (z)
select * from users where id in (x,y) //(creator, approver)

at the moment I have these relations:

public function created_by()
{
    return $this->belongsTo(User::class, 'creator_id');
}

public function approved_by()
{
    return $this->belongsTo(User::class, 'approved_by_id');
}

CodePudding user response：

I guarantee you are not running a dataset big enough for performance of eager loading to be a concern. But, if having multiple queries bothers you and you'd rather incur the technical debt of managing a query builder command, I think this should work:

$post = Post::select('posts.*')
    ->selectRaw('creator.name as creator_name')
    ->selectRaw('approver.name as approver_name')
    ->leftJoin('users as creator', 'posts.creator_id', 'creator.id')
    ->leftJoin('users as approver', 'posts.approved_by_id', 'approver.id')
    ->where('posts.id', $post_id)
    ->get();

We just join the users table twice with different aliases; to avoid overwriting the column values they need to be aliased as well. Your data will be a different format of course, you won't have access to things like $post->created_by->name but will instead rely on the column aliases.

The equivalent SQL query would be:

SELECT posts.*, creator.name AS creator_name, approver.name AS approver_name
FROM posts
LEFT JOIN users AS creator ON (posts.creator_id = creator.id)
LEFT JOIN users AS approver ON (posts.approved_by_id = approver.id)
WHERE posts.id = ?

CodePudding user response：

If I understood the question correctly

User::with(['created_by', 'approved_by'])
   ->get()