Avoid Pivot Table and Use Json Column in Laravel

Hello devs

In this tutorial i show you how we can avoid pivot table and how we can do same stuff without making pivot table. Simply think we make many to many relationship then sometimes we need to make pivot table like one post has many categories and one categories has many post. That time we use pivot table like post_categories.

But in this table we will show our data like post with categories, or post according to category using json column in our post table. For avoiding pivot table 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 remove 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