I’ve a posts table with following structure
| id |
title |
description |
| 1 |
Post A |
Description A |
| 2 |
Post B |
Description B |
reactions table with following structure:
| id |
title |
url |
| 1 |
Like |
http://like_url |
| 2 |
Dislike |
http://dislike_url |
And post_reactions pivot table with following structure
| post_id |
user_id |
reaction_id |
| 1 |
1 |
1 |
| 1 |
2 |
1 |
| 2 |
1 |
2 |
How can I achieve posts table result OrderBy Max Count of ReactionID?
For e.g.
- By ordering with reactionId = 1, the result should be:
[
{
id: 1,
title: 'Post A',
description: 'Description A'
reaction_count: 2 // count of reactionId=1 is maximum here
},
{
id: 2,
title: 'Post B',
description: 'Description B'
reaction_count: 1
}
]
- By ordering with reactionId = 2, the result should be:
[
{
id: 2,
title: 'Post B',
description: 'Description B'
reaction_count: 1 // count of reactionId=2 is maximum here
},
{
id: 1,
title: 'Post A',
description: 'Description A'
reaction_count: 2
},
]
What I’d tried is:
// Post Model
public function reactions(): BelongsToMany
{
return $this->belongsToMany(Reaction::class)->withPivot('user_id');
}
// Controller
Post::with(['post_reactions' => function ($query) use ($reactionId) {
$query->where('post_reactions.reaction_id', $reactionId)
->select(DB::raw('count(post_reactions.reaction_id) as reaction_count'))
->get();
}])->get();
