SELECT
posts.id,
(select count(*) from post_likes where post_id = 13 and user_id = 12) as post_like
FROM
posts
LIMIT 5
How to write this query in Laravel query builder?
Add a comment
|
1 Answer
If your ORM models are defined (and you have both Post and PostLike models), create a relationship in your Post.php model (if not already), like:
public function likes(){
return $this->hasMany(PostLike::class);
}
Then if you only need the count, try something like:
$userId = 12;
$postList = Post::query()
->whereId(13)
->withCount(['likes', 'likes AS post_like' => function ($query) use($userId) {
$query->where('user_id', '=', $userId);
}])
->limit(5)
->get();
// Then do something with result.
foreach ($postList as $post) {
$count = $post['post_like'];
}
Note that above we use
post_likealias, and limit touser_id, just to much OP requirements; Else we could simply setlikes_countto the number of relations, like:->withCount('likes')
But you could use relationship for subquery with the whereHas(...) eloquent method, like:
Post::query()->whereHas('likes', function($query){
$query->where(...your statements for sub query go there);
}, '>', 4)->limit(5)->get(); //Select where more than 4 relation found with given parameters
For more see: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence
