Retrieving the Count of Records in an Eloquent Pivot Table in Laravel 5


 AMP

While migrating this blog from Wolf CMS to Laravel, I needed to write some custom code to re-implement 'tags' functionality for blog posts. In Wolf CMS I relied on the Tagger plugin which has a 'count' field on the tags table like so:

mysql> desc tags;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)     | NO   | UNI | NULL    |                |
| count      | int(10) unsigned | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

So while creating the database schema in Laravel I included a similar 'count' field on my tags table, and used Jeffrey Way's Laravel 5 Generators Extended package to generate the intermediate pivot table for the Posts-Tags relationship with the following command:

php artisan make:migration:pivot tags posts

According to the Elquent Relationships Laravel 5.2 documentation page once the Eloquent 'many-to-many' relationship between posts and tags is in place, the 'withCount' method can be used to include a 'posts_count' field on the tag model. This 'posts_count' field contains the value of how many posts are tagged with a given tag, and can be used to update the 'count' column on our tag model. The following is an example of a method which goes through all tags and updates the 'count' field on the tag model if it doesn't match the actual count according to the pivot table:

public static function updateCounts()
{
  foreach(Tag::withCount('posts')->get() as $tag)
  {
    if($tag->count != $tag->posts_count)
    {
      $tag->count = $tag->posts_count;
      $tag->save();
    }
  }
}


Comments:


Gravatar of Kyle Jones

Knowledge Base is a repository for various notes, guides, and thoughts on a wide range of IT related topics. Read more...