He7nG
He7nG

Reputation: 395

How to fix error Base table or view not found: 1146 Table laravel relationship table?

I am a new of laravel I try to create relationship many to many between table,My problem when I am insert data in to database I got errors

QueryException in Connection.php line 713: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'learn.category_posts' doesn't exist (SQL: insert into category_posts (category_id, posts_id) values (4, ))

can anyone help me pls . and here below is my migrate and code:

2016_08_04_131009_create_table_posts.php

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->increments('id');
        $table->text('title');
        $table->text('body');
        $table->timestamps();
    });
}

2016_08_04_131053_create_table_categories.php

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });
}

2016_08_04_131413_create_table_category_posts.php

public function up()
{
    Schema::create('category_post', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('category_id')->unsigned();
        $table->integer('post_id')->unsigned();
        $table->foreign('category_id')->references('id')->on('categories')->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('post_id')->references('id')->on('posts')->onUpdate('cascade')->onDelete('cascade');
        $table->timestamps();
    });
}

and my model Posts.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Posts extends Model
{
    protected $table = 'posts';

    public function categories()
    {
        return $this->belongsToMany('App\Category');
    }
}

Category.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $table = 'categories'; 

    public function posts()
    {
        return $this->belongsToMany('App\Posts');
    }   
}

My PostsController.php

public function create()
{
    $categories = Category::all();
    return view('create',compact('categories'));
}

public function store(Request $request)
{
   $post = new Posts;
   $post->title = $request->title;
   $post->body = $request->body;
   $post->categories()->attach($request->categories_id);

    return redirect()->route('posts.index');
}

My View create.blade.php

{!!Form::open(array('route' => 'store', 'method' => 'POST'))!!}

    {{Form::text('title')}}<br>
    {{Form::textarea('body')}}<br>
    <select name="categories_id" multiple>
        @foreach ($categories as $category)
            <option value="{{ $category->id }}">{{ $category->name }}</option>
        @endforeach
    </select>
    <br>
    {{Form::submit('submit')}}

{!!Form::close()!!}

Upvotes: 35

Views: 276981

Answers (15)

Shakeel Ahmad
Shakeel Ahmad

Reputation: 349

You can add the table name in a relationship, Laravel follows alphabetical order to generate the pivot table name for a many-to-many relationship.

return $this->belongsToMany(Model::class, 'table_name');

Upvotes: 0

Mithun Deshan
Mithun Deshan

Reputation: 83

Add this to your model

protected $table = 'your table name';

Upvotes: 2

Ahmed Mohamed
Ahmed Mohamed

Reputation: 89

If you are getting the error "SQLSTATE[42S02]: Base table or view not found" and you have no problem in your code, check if you have wrote the first letter of the table in capital letter.

Inspections >> inspections

capital case and small case letters matters on AWS environments.

Upvotes: 0

Menisha Myelwaganam
Menisha Myelwaganam

Reputation: 266

Just run the command:

php artisan migrate:refresh --seed

Upvotes: 0

Ali Raza Lilani
Ali Raza Lilani

Reputation: 96

If you're facing this error but your issue is different and you're tired of searching for a long time then this might help you.

If you have changed your database and updated .env file and still facing same issue then you should check C:\xampp\htdocs{your-project-name}\bootstrap\cache\config.php file and replace or remove the old database name and other changed items.

Upvotes: 0

MD SHAYON
MD SHAYON

Reputation: 8063

This problem occur due to wrong spell or undefined database name. Make sure your database name, table name and all column name is same as from phpmyadmin

Upvotes: 0

Anand Mainali
Anand Mainali

Reputation: 1073

You can add this in Post Model,

public function categories()
{
 return $this->belongsToMany('App\Category','category_post','post_id','category_id');                 
}

category_post indicate the table you want to use.
post_id indicate the column where you want to store the posts id.
category_id indicate the column where you want to store the categories id.

Upvotes: 9

Haritsinh Gohil
Haritsinh Gohil

Reputation: 6282

For solving your Base Table or view not found error you can do As @Alexey Mezenin said that change table name category_post to category_posts,

but if you don't want to change the name like in my case i am using inventory table so i don't want to suffix it by s so i will provide table name in model as protected $table = 'Table_name_as_you_want' and then there is no need to change table name:

Change your Model of the module in which you are getting error for example:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Inventory extends Model
{
    protected $table = 'inventory';

    protected $fillable = [
        'supply', 'order',
    ];
}

you have to provide table name in model then it will not give error.

Upvotes: 8

luis
luis

Reputation: 202

try checking in the app if you are using the tables before it's created such as appServiceProvider.php

you might be calling the table without being created it, if you are, comment it then run php artisan migrate.

Upvotes: 0

Shal
Shal

Reputation: 633

Schema::table is to modify an existing table, use Schema::create to create new.

Upvotes: 20

Aldrin Masamoc Mojica
Aldrin Masamoc Mojica

Reputation: 221

The main problem for causing your table unable to migrate, is that you have running query on your "AppServiceProvider.php" try to check your serviceprovider and disable code for the meantime, and run php artisan migrate

Upvotes: 17

Chathura D Ranathunga
Chathura D Ranathunga

Reputation: 51

The simplest thing to do is, change the default table name assigned for the model. Simply put following code, protected $table = 'category_posts'; instead of protected $table = 'posts'; then it'll do the trick.

However, if you refer Laravel documentation you'll find the answer. Here what it says,

By convention, the "snake case", plural name of the class(model) will be used as the table name unless another name is explicitly specified

Better to you use artisan command to make model and the migration file at the same time, use the following command,

php artisan make:model Test --migration

This will create a model class and a migration class in your Laravel project. Let's say it created following files,

Test.php

2018_06_22_142912_create_tests_table.php

If you look at the code in those two files you'll see,

2018_06_22_142912_create_tests_table.php files' up function,

 public function up()
 {
      Schema::create('tests', function (Blueprint $table) {
      $table->increments('id');
      $table->timestamps();
      });
 }

Here it automatically generated code with the table name of 'tests' which is the plural name of that class which is in Test.php file.

Upvotes: 2

jpasosa
jpasosa

Reputation: 481

Laravel tries to guess the name of the table, you have to specify it directly so that it does not give you that error..

Try this:

class NameModel extends Model {
    public $table = 'name_exact_of_the_table';

I hope that helps!

Upvotes: 37

Steven Grauwmans
Steven Grauwmans

Reputation: 196

You should change/add in your PostController: (and change PostsController to PostController)

public function create()
{
    $categories = Category::all();
    return view('create',compact('categories'));
}

public function store(Request $request)
{
    $post = new Posts;
    $post->title = $request->get('title'); // CHANGE THIS
    $post->body = $request->get('body'); // CHANGE THIS
    $post->save(); // ADD THIS
    $post->categories()->attach($request->get('categories_id')); // CHANGE THIS

    return redirect()->route('posts.index'); // PS ON THIS ONE
}

PS: using route() means you have named your route as such

Route::get('example', 'ExampleController@getExample')->name('getExample');

UPDATE

The comments above are also right, change your 'Posts' Model to 'Post'

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163978

It seems Laravel is trying to use category_posts table (because of many-to-many relationship). But you don't have this table, because you've created category_post table. Change name of the table to category_posts.

Upvotes: 35

Related Questions