Laravel - belongsToMany efficient tag count?

Question

Given the following belongsToMany relationship:

Tag Model:

class Tag extends Model
{
    public function posts (){
        return $this->belongsToMany('App\Post');
    }
}

Post Model:

class Post extends Model
{
    public function tags (){
        return $this->belongsToMany('App\Tag');
    }
}

What would be the most efficient and scalable way in laravel to query for the count of each tag, and order based on tag count from lowest to highest?

Answer 1

In order to get tags and their count you need to join tags table in order to fetch Tag data with post_tag table to get the number of times given tag was used to tag a post. You can do that with the following code:

// get Tag data and join with post_tag
$counts = Tag::join('post_tag', 'tags.id', '=', 'post_tag.tag_id')
// group by tags.id in order to count number of rows in join and to get each tag only once
->groupBy('tags.id')
// get only columns from tags table along with aggregate COUNT column    
->select(['tags.*', DB::raw('COUNT(*) as cnt')])
// order by count in descending order
->orderBy('cnt', 'desc')
->get();

This will give you a collection of Tag objects. You will find the count in their cnt attribute.

Answer 2

You could use the withCount method

If you want to count the number of results from a relationship without actually loading them you may use the withCount method

$posts = App\Post::withCount('tags')->get();

foreach ($posts as $post) {
    echo $post->tags_count;
}