timgavin
6 months ago

Incrementing a count field in another table

Posted 6 months ago by timgavin

I'm creating a simple Categories system to assign categories to posts. Basically a tagging system.

As you can see by my migrations I have two tables: one for categories and one for the Posts to which each category is attached.

Schema::create('categories', function (Blueprint $table) {
    $table->increments('id');
    $table->string('slug')->unique();
    $table->string('name')->unique();
    $table->unsignedInteger('count')->default(0)->index();
    $table->timestamps();
});

Schema::create('categories_posts', function (Blueprint $table) {
    $table->unsignedInteger('category_id')->index();
    $table->unsignedInteger('post_id')->index();
    $table->unique(['category_id', 'post_id']);
    $table->timestamps();
});

When a category is attached to a Post I want to increment a count field in the categories table, to show how many posts are using that category.

Here's how I'm currently doing it...

$post = Post::create(['title' => 'My First Post']);
$post->addCategory([1,2,3]); // <-- the category IDs from a dropdown
// App\Post.php

public function addCategory($array)
{
    $data = $ids = [];
    
    foreach ($array as $category_id) {
        $arr['post_id'] = $this->id;
        $arr['category_id'] = $category_id;
        array_push($data, $arr);
        array_push($ids, $category_id);
    }

    \App\CategoryPost::insert($data);

    // increment the count field
    foreach($ids as $id) {
        \App\Category::where('id', $id)->increment('count');
    }
}

Is there a more elegant way of doing this?

Is there a way to update the count field in CategoryPost without resorting to foreach? Or a way to cascade the increment, such as with foreign keys?

Please sign in or create an account to participate in this conversation.