如何根据数据透视表中的属性计算数据量？

Question

i am a beginner in Laravel 5, and im trying to develop a blog, let's say i have an Article model which has many to many relation with Tag model.

This is the Article model:

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Article extends Model {

    protected $fillable = [
        'name',
        'description'
    ];

    public function tags() {
        return $this->belongsToMany('App\Tag')->withTimestamps();
    }

}

This is the Tag model:

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Tag extends Model {

    public function articles() {
        return $this->belongsToMany('App\Article')->withTimestamps();
    }

}

Here is the migration for the Articles table and the Article_Tag pivot table:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateArticlesTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('articles', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name')->unique();
            $table->text('description');
            $table->timestamps();
        });

        Schema::create('article_tag', function(Blueprint $table)
        {
            $table->integer('article_id')->unsigned()->index();
            $table->foreign('article_id')->references('id')->on('articles')->onDelete('cascade');

            $table->integer('tag_id')->unsigned()->index();
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('article_tag');
        Schema::drop('articles');
    }

}

This is the migration for the tags table:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTagsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tags', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name')->unique();
            $table->integer('count')->unsigned();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('tags');
    }

}

So lets say, i have 3 tags in my database, "lifestyle", "social", "economy".

Assume that an article is created with tag "lifestyle", then in the ArticleController, in the store function, i want the count attribute on the "lifestyle" tag to be set to the amount of the articles in the database (in this case, it will count from the pivot table) which has the "lifestyle" tag, using the count() function.

If the tag_id is in the articles table, i can do it like this in the controller:

$article = new Article($request->all());
$article->save();

foreach($article->tags as $tag) {
    $tag->count = Article::where('tag_id', $request->tag_id)->count();
    $tag->save();
}

But in this case, the where function defers to a column in the pivot table, and i had no idea how to do it.

Any solution for my beginner's error? Thanks in advance.

Answer 1

You can easily count articles that belong to given tag by calling:

$tag->count = $tag->articles()->count();
$tag->save();

The above will run 2 SQL queries - one to do the count and one to update the tag. You could also try the following, that would do a single query:

Tag::whereId($tag->id)->increment('count');

This will run a single SQL UPDATE query:

UPDATE `tags` SET `count` = `count` + 1 WHERE `id` = {$tag->id}