Laravel Order results by conditional counting Pivot Table

Question

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:: select(array
    ('*', \DB::raw('(SELECT count(*)
    FROM post_reactions 
    WHERE post_reactions.reaction_id ='. $reactionId.') as reaction_counts'))

   );
    $query->orderBy('reaction_counts', 'desc');

Note: I need all the post sorted by the count of the reactions given reaction_id

Ross_102 · Answer

You can use https://laravel.com/docs/9.x/eloquent-relationships#counting-related-models:

$posts = Post::withCount('reactions')->orderBy('reactions_count')->get();

foreach($posts as $post) {
    //display reactions count
    echo $post->reactions_count;
}

Laravel Order results by conditional counting Pivot Table

1 Answers