Avoid Pivot Table and Use Json Column in Laravel

Hello devs

In this tutorial, I show you how we can avoid pivot tables and how we can do the same stuff without making pivot tables. Simply think we make many to many relationships then sometimes we need to make pivot table like one post has many categories and one category has many posts.

That time we use pivot tables like post_categories. But in this table, we will show our data like posts with categories, or post according to categories using the JSON column in our post table.

For avoiding pivot tables we can reduce one table from our database schema and of course, our database performance will be better than before. You will also learn how to insert JSON data into MySQL using Laravel. 

Which one is better for connection? Pivot table or JSON? You will be clear from this article that laravel removes pivot from JSON in laravel. So let's start our tutorial laravel model JSON column.

Preview : Post table after inerting data

Preview : Post with category name

Preview : Post according to category id

Step 1 : Create Model

In this we need two model. One is category model and other is post model. So let's create it.

php artisan make:model Post -m
php artisan make:model Category -m

Now open both model and update like below image.

app/Post.php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\Casts\Json;

class Post extends Model
{
    protected $guarded = [];

    protected $casts = [
        'category_id' => Json::class
    ];

}

and open migration file and update it like below.

database/migration/create_posts_table.php

public function up()
 {
   Schema::create('posts', function (Blueprint $table) {
      $table->id();
      $table->json('category_id');
      $table->string('title');
      $table->timestamps();
   });
 }

Step 2 : Create Route

We need many route for creating post or showing post according to category. So open web.php and update it like below.

routes/web.php

Route::name('admin.')->namespace('Admin')->prefix('admin')->group(function () {

//Post Route
   Route::get('post/create','PostController@show_post_form')->name('post.create');
   Route::post('post/create','PostController@store');
   Route::get('posts','PostController@index')->name('post.index');
   Route::get('category/{id}','PostController@category_post')->name('category');

});

Step 3 : Create Controller 

In this step we need to create post controller. So create it by the following command.

php artisan make:controller Admin/PostController

And update it like below.

app/Http/Controllers/Admin/PostController.php

namespace App\Http\Controllers\Admin;

use App\Category;
use App\Http\Controllers\Controller;
use App\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function show_post_form()
    {
    	return view('admin.post.create');
    }

    public function store(Request $request)
    {
    	$request->validate([
          'category_id' => 'required',
          'title' => 'required'
    	]);

    	$post = new Post;
    	$post->category_id = json_encode($request->category_id);
    	$post->title = $request->title;
    	$post->save();
        
        return redirect()->back();

    }

    public function index()
    {   
    	$post = Post::all();

    	return view('admin.post.index',['posts' => $post]);
    }

    public function category_post($id)
    {   
      return Post::whereJsonContains('category_id',$id)->get();
    }
}

Step 4 : Create Blade File

Now we are in the final step. So how to insert json data into mysql using laravel we will know that now. No create below file and paste this code in your file.

resources/views/admin/post/create.blade.php

Make sure you have setup select2 box for multiple category select. 

resources/views/admin/post/index.blade.php

Read aslo : Advanced Search Filter using Dropdown in Laravel

Hope this how to use laravel model json column tutorial will help you.