Laravel many-to-many relation with custom table names and IDs

Question

Hello so I have many to many relation between Question [table name: tblquestion, id: que_id] and Agecategory [table name: tblagecategory, id: aca_id]. They have shared table named QuestionAgecategory [table name: tblquestionagecategory, id: qac_id].

I want to note that all the IDS and table names are custom named and not according to typical Laravel syntax.

And I am trying to relate them in Laravel. So far it returns null when I try to look $question->agecategories;

$question->agecategories; => null

But it has records in it and returns this after $question = App\Question::find(1);

$question = App\Question::find(1); => App\Question {#2901 que_id: 1, que_name: "hello",

Question model

class Question extends Model
{
    protected $table = 'tblquestion';
    protected $primaryKey = 'que_id';
    protected $keyType = 'integer'; 
    public $incrementing = true;
    public $timestamps = false;

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

Agecategory model

class Agecategory extends Model
{
    protected $table = 'tblagecategory';
    protected $primaryKey = 'aca_id';
    protected $keyType = 'integer'; 
    public $incrementing = true;

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

QuestionAgecategory model

class QuestionAgecategory extends Model
{
    protected $table = 'tblquestionagecategory';
    protected $primaryKey = 'qac_id';
    protected $keyType = 'integer'; 
    public $incrementing = true;
}

Migrations

      Schema::create('tblquestion', function (Blueprint $table) {
          $table->increments('que_id');
          $table->string('que_name', 128);
      });


      Schema::create('tblagecategory', function (Blueprint $table) {
          $table->increments('aca_id');
          $table->timestamps();
      });

      Schema::create('tblquestionagecategory', function (Blueprint $table) {
          $table->increments('qac_id');
          $table->integer('qac_que_id')->unsigned();
          $table->integer('qac_aca_id')->unsigned();
          $table->foreign('qac_que_id')->references('que_id')->on('tblquestion');
          $table->foreign('qac_aca_id')->references('aca_id')->on('tblagecategory');
      });

score 14 · Accepted Answer · edited Aug 23 '18 at 16:05

14

You are using custom columns and custom database naming.

Your Belongs to many is expecting a pivot table tblquestion_tblagecategory which does not exist. As the previos answer stated, you should change your belongsToMany to search for the custom tables and columns.

https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

Change to this in your question Model

public function agecategories() 
{
    return $this->belongsToMany('App\Agecategory', 'tblquestionagecategory', 'qac_que_id', 'qac_aca_id');
}

And also, in your other Agecategory Model

public function questions() 
{
    return $this->belongsToMany('App\Question', 'tblquestionagecategory', 'qac_aca_id', 'qac_que_id');
}

edited Aug 23 '18 at 16:05

answered Aug 23 '18 at 09:43

Erubiel

2,934
14
32

I still get this error: $agecategory->questions; Illuminate/Database/QueryException with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'yamldb.agecategory_question' doesn't exist (SQL: select `tblquestion`.*, `agecategory_question`.`agecategory_aca_id` as `pivot_agecategory_aca_id`, `agecategory_question`.`question_que_id` as `pivot_question_que_id` from `tblquestion` inner join `agecategory_question` on `tblquestion`.`que_id` = `agecategory_question`.`question_que_id` where `agecategory_question`.`agecategory_aca_id` = 1)' – Tinabot Aug 23 '18 at 09:48
thats when you make this changes? that's weird, because you are defining the table name 'tblquestionagecategory' and thats what it should look for... – Erubiel Aug 23 '18 at 09:50
Yeah I know. And I really can't do much about it because I am getting these table names and all the details from the yaml file so that's why the table names are not usual to Laravel syntax. Also I messed a bit with the ID's (first I tried your solution and then I tried adding ID from the Questions table and connect it with the ID in the tblquestionagecategory's ID `public function questions() { return $this->belongsToMany('App\Question', 'tblquestionagecategory', 'aca_id', 'qac_aca_id'); }` But that also didn't work. – Tinabot Aug 23 '18 at 09:55
im taking a long shot... but maybe composer dumpauto or php artisan dump-autoload – Erubiel Aug 23 '18 at 10:01
Now I am getting this error. I mean now at least it looks into right table? `>>> $question->agecategories; Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tblquestionagecategory.que_id' in 'field list' (SQL: select `tblagecategory`.*, `tblquestionagecategory`.`que_id` as `pivot_que_id`, `tblquestionagecategory`.`qac_que_id` as `pivot_qac_que_id` from `tblagecategory` inner join `tblquestionagecategory` on `tblagecategory`.`aca_id` = `tblquestionagecategory`.`qac_que_id` where `tblquestionagecategory`.`que_id` = 1)'` – Tinabot Aug 23 '18 at 10:11
No, belongsToMany needs: 1 Related Model, 2 Specific pivot table (which works now) 3 foreign key of pivot table for Left side of the relation Model, 4 foreign key of pivot table for Related Model (1) --- it should be 'App\Question', 'tblquestionagecategory', 'qac_aca_id', 'qac_que_id' – Erubiel Aug 23 '18 at 10:15
1

yah I know i switched it back to these keys but it still takes the old values even after I tried `composer dump-autoload` again . But the function from the Agecategory works! So it is just from the Question model function that still is stubborn. But I will mark this reply as my question answer :) – Tinabot Aug 23 '18 at 10:22
Always recheck, also, if you are using homestead... (it shouldnt but) there may be some lag updating the files you modify outside homestead into homestead. – Erubiel Aug 23 '18 at 10:31
Hey I know it's been a while but i was wondering do I need to specify any relations in the middle model - QuestionAgecategory ? – Tinabot Aug 27 '18 at 10:18

score 0 · Answer 2 · edited Aug 23 '18 at 18:50

0

add below relationship function in Question model

public function agecategories() 
{
    return $this->belongsToMany('App\Agecategory', 'tblquestionagecategory', 'qac_que_id', 'qac_aca_id');
}

edited Aug 23 '18 at 18:50

answered Aug 23 '18 at 09:37

Jinal Somaiya

1,931
15
29

it still returns null even when I added the tblquestionagecategory and the keys. I was thinking that i needed to add the table. but it is really tricky with custom names making relations work. do i need to define something in the QuestionAgecategory model maybe? – Tinabot Aug 23 '18 at 09:43
QuestionAgecategory model is not needed. – Jinal Somaiya Aug 23 '18 at 09:48

score 0 · Answer 3 · answered Apr 29 '20 at 11:39

0

I encountered the same problem but I had to declare only the foreign keys without a prefix like this:

public function agecategories() 
{
    return $this->belongsToMany('App\Agecategory', 'tblquestionagecategory', 'que_id', 'aca_id');
}

answered Apr 29 '20 at 11:39

Mohammad Tbeishat

961
6
15

Laravel many-to-many relation with custom table names and IDs

Question model

Agecategory model

QuestionAgecategory model

Migrations

3 Answers3