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');
      });

Mauney

Answer 1

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');
}

Oxytocin

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)' – Mauney

thats when you make this changes? that's weird, because you are defining the table name 'tblquestionagecategory' and thats what it should look for... – Oxytocin

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. – Mauney

im taking a long shot... but maybe composer dumpauto or php artisan dump-autoload – Oxytocin

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)'` – Mauney

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' – Oxytocin

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 :) – Mauney

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. – Oxytocin

Hey I know it's been a while but i was wondering do I need to specify any relations in the middle model - QuestionAgecategory ? – Mauney

Answer 2

add below relationship function in Question model

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

Crossfertilization

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? – Mauney

QuestionAgecategory model is not needed. – Crossfertilization

Answer 3

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');
}

Wiebmer