Reputation: 2932
I have 3 tables:
professor | id | name
discipline | id | name
and one to link them
disc_prof | prof_id | disc_id
How do I retrieve data as an Inner Join
?
Is it something like:
SELECT prof.name, prof.id, disc.id, disc.name
FROM disc_prof AS dc INNER JOIN professor as prof ON prof.id = dc.prof_id
INNER JOIN discipline AS disc ON dc.disc_id = disc.id
What I did so far:
class Professor extends Model
{
protected $table = "professor";
public function disc()
{
return $this->belongsToMany('App\Discipline');
}
}
class Disciplina extends Model
{
protected $table = "discipline";
}
But I'm getting the following error:
Base table or view not found: 1146 Table 'database.discipline_professor' doesn't exist
It's not database.discipline_professor
, it's database.disc_prof
.
What is the correct way to change it ?
Upvotes: 0
Views: 966
Reputation: 8159
So Eloquent automatically builds your table names for pivots. It takes the relationship table names, in this case professor
and discipline
, arranges them in alphabetical order, and makes each word singular. That's where discipline_professor
comes from. A pivot table for users
and regions
would be region_user
, as another example. The two IDs for the table are then taken from the relationship name and the table name, so it would expect professor_id
and discipline_id
.
You can get around that when you create the relationship. If you look at the docs for Model::belongsToMany()
, you will see the arguments are:
string $related, string $table = null, string $foreignKey = null, string $otherKey = null, string $relation = null
So you need to set $table
, $foreignKey
, and $otherKey
. In your case:
class Professor extends Model
{
protected $table = "professor";
public function disc()
{
return $this->belongsToMany('App\Discipline', 'disc_prof', 'prof_id', 'disc_id');
}
}
As a sidenote, I highly suggest using the recommended names with Eloquent. All tables should be plural, all pivots should match what I stated above, all foreign keys should be the relationship name in singular form + _id
, etc. It will make working with Eloquent much, much easier and faster. You can find some of the details of what Eloquent expects in their Relationships documentation. There is no clear cut list of expectations from what I have been able to find, so that will have to do.
Upvotes: 1