Eloquent table relations

Question

I am trying to pull a list of characters that belong to a certain user. When I make the request I get an SQL Error. Reading through the error it is trying to us fields that don't exist.

Error:

 SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'characters' (SQL: select `characters`.*, `characters`.`id` as `pivot_id`, 
 `characters`.`character_id` as `pivot_character_id` 
 from `characters` inner join `characters` on `characters`.`id` = `characters`.`character_id` where `characters`.`id` = 1)

"character_id" does not exist in my database. The problem is I can't find where Eloquent is making that field. I looked through the source code and there was a lot of "If this is not provided use $variable.'_id'. I could not find that code anywhere for this though.

Models are below.

class Character extends Eloquent {

    protected $guarded = array('id');

    protected $table = 'characters';

    public function User ()
    {
        return $this->belongsTo('User', 'id');
    }
}


class User extends Eloquent implements UserInterface, RemindableInterface {

    use UserTrait, RemindableTrait;

    protected $table = 'users';

    protected $hidden = ['password', 'remember_token'];

    protected $guarded = ['password'];

    public function Character ()
    {
        return $this->belongsToMany('Character', 'characters', 'id');
    }
}     

There is a foreign key between user_id in the characters table, and id in the users table.

Answer 1

belongsToMany is for many-to-many relations. Laravel throws you an error because it expects third table - pivot table - containing both character_id and user_id.

If you dont want many-to-many but one-to-many then you should use hasMany and belongsTo.