Hey guys, I have troubles with a certain query. Really would appreciate your help. I need to fetch a post, and its latest comment, from the database. One post can have multiple comments. So I would need a join, for the first part. But, how do I add order, limit, and possibly a where statement, on the right site of the join (the comments). Thx in advance.
Make the appropriate relationship:
// Post
public function latestComment()
{
return $this->hasOne(Comment::class)->latestOfMany();
}
$post = Post::with('latestComment')->find($postId);
$post->latestComment; // is a Comment instance
https://laravel.com/docs/9.x/eloquent-relationships#has-one-of-many
@tykus Thank you. How would you do it with query builder?
@ivymaster I wouldn't do it with the Query Builder at all : )
@tykus In this situation I need to. I used laravel dubugger, to see how laravel implements this relationship. It uses 2 queries, to query it separately, as much as I can see. I have to query a large database, and I have heard using Query builder, alongside one query, would be more benfitial.
I have heard using Query builder, alongside one query, would be more benfitial
Loading only what's needed, related data is more efficient than joins. Always query only what's needed.
Joins transverse whole table.
@ivymaster two simple queries can be more efficient than a join - especially when the needs are simple, and the complications from the join are significant (ie, cannot use model or accessors)
I wouldn't use a join unless its clear that I have a performance problem. Never happened so far
@ivymaster I would be suspicious of this type of optimisation - is your database so overloaded that replacing a additional query is more beneficial with a JOIN? You lose the benefit of having a Comment model instance to represent your comment!
If you absolutely must get the Post and Comment as one object; you can use a window function similar to this (I have not tested)
SELECT posts.*, latest_comments*
FROM posts
LEFT JOIN (
SELECT
comment.*,
RANK() OVER (PARTITION BY post_id ORDER BY created_at DESC) last_comment
FROM comments
WHERE post_id = <<<$id>>>
) latest_comments
WHERE posts.id = <<<$id>>>
@tykus thanks, it works in filament
For Eloquent, I'm assuming you have a relationship between models
$posts = Post::with(['comments' => function($query) {
$query->orderBy('id', 'DESC')->where('column', '=', 'value')->limit(5); // You can chain more methods
}])->find($postId);
// Then you can use
$post->comments
For join statement
DB::table('posts')
->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
->limit(5)
->where('column', '=', 'value')
->get()
And please see @tykus and @jlrdw notes on eloquent and join statements.
Please or to participate in this conversation.