0

Please someone help me on this. I am new to laravel and now I need to store an array from a select tag to mysql and I am having this erorr. SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cmsystem.account_code_project' doesn't exist (SQL: select account_code_ac_id from account_code_project where project_pj_id = 1). Seems like it has something to do with using belongsToMany() fucntion. What should I do here is my code.

here's the project_table

 //project table
       Schema::create('projects', function(Blueprint $table)
    {
        $table->increments('pj_id');
        $table->string('pj_title',100);
        $table->string('pj_description');
    });

here's the accountcodes table

 //codes
       Schema::create('accountcodes', function(Blueprint $table)
    {
        $table->increments('ac_id');
        $table->string('ac_code',100)->nullable();
        $table->string('ac_description')->nullable();
    });

here's the intermediate table for the two tables

//intermediate table
    Schema::create('code_project', function(Blueprint $table){
        $table->increments('id');
        $table->integer('project_id')->unsigned();
        $table->foreign('project_id')->references('pj_id')->on('projects');

        $table->integer('account_id')->unsigned();
        $table->foreign('account_id')->references('ac_id')->on('accountcodes');
    });

Project Controller

//ProjectController
   public function store(Request $request)
    {
    $this->validate($request,[
        'pj_title'=>'required',
        'pj_description'=>'required',
        ]);

    $project = new project;
    $project->pj_title = $request->pj_title;
    $project->pj_description = $request->pj_description;
    $project->save();

    $project->accounts()->sync($request->accounts, false);
    return redirect('/projectlist'); 
    }

Models

//AccountCode
  protected $table = 'accountcodes';
     public function projects()
{
    return $this->belongsToMany('App\Project');
}

//Project
  protected $table = 'projects';
    public function accounts()
{
    return $this->belongsToMany('App\AccountCode');
}

Thank you.

kalawadei
  • 25
  • 1
  • 7

3 Answers3

1

You can hint the table name in belongsToMany

return $this->belongsToMany('App\AccountCode',"code_project");

This is because by convention your pivot table should have been named: accountcode_project (which also doesn't reflect your model name which should have been Accountcode)

apokryfos
  • 38,771
  • 9
  • 70
  • 114
  • should I need to specify it on both of my Model? I mean like this return $this->belongsToMany('App\Project', 'code_project'); return $this->belongsToMany('App\AccountCode', 'code_project'); – kalawadei Aug 08 '17 at 09:24
  • @ClaudeDizon yes, wherever you need to use that pivot table. The problem is you're not following the naming conventions. – apokryfos Aug 08 '17 at 09:53
  • adding "code_project" on belongsToMany tag doesn't change anything so what I did is I changed the table name to "accountcode_project". And still the error is still the same. I don't see any error on this. what sould I do? sorry and thanks. – kalawadei Aug 09 '17 at 02:18
  • @kalawadei Laravel will assume that if your model name is `AccountCode` then your table name is `account_code` try renaming your model to `Accoundcode` (no upper case in middle) to see if that helps – apokryfos Aug 09 '17 at 06:02
1

This happens because you have to specify the pivot table name in model relationship:

return $this->belongsToMany('App\Project', 'code_project');

Otherwise Eloquent tries to detect the default table name following its conventions, which I encourage you to follow.

xyzale
  • 745
  • 8
  • 14
  • should I need to specify it on both of my Model? I mean like this return $this->belongsToMany('App\Project', 'code_project'); return $this->belongsToMany('App\AccountCode', 'code_project'); – kalawadei Aug 08 '17 at 09:23
  • adding "code_project" on belongsToMany tag doesn't change anything so what I did is I changed the table name to "accountcode_project". And still the error is still the same. I don't see any error on this. what sould I do? sorry and thanks – kalawadei Aug 09 '17 at 02:19
0

alright I solved it by declaring both the representative of both Model so it looks like this 

//AccountCode
      public function projects()
{
    return $this->belongsToMany('App\Project', 'accountcode_project', 'ac_id', 'pj_id');
}


// Project
    public function accountcodes()
{
    return $this->belongsToMany('App\AccountCode', 'accountcode_project', 'pj_id', 'ac_id');
}
kalawadei
  • 25
  • 1
  • 7