Ad

Laravel - BelongsToMany Efficient Tag Count?

- 1 answer

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?

Ad

Answer

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.

Ad
source: stackoverflow.com
Ad