Laravel-admin: relation many-to-many does't work

Question

I use laravel-admin (https://laravel-admin.org) in my laravel project. I wish to show on User admin panel role names of user. But I get error message:

SQLSTATE[42703]: Undefined column: 7 ERROR: column users_roles.role___id does not exist LINE 1: ...les" inner join "users_roles" on "roles"."__id" = "users_rol... ^ (SQL: select "roles".*, "users_roles"."user___id" as "pivot_user___id", "users_roles"."role___id" as "pivot_role___id" from "roles" inner join "users_roles" on "roles"."__id" = "users_roles"."role___id" where "users_roles"."user___id" in (1, 2, 3))

Here are my classes and controller:

User.php:

 
namespace App\Models;

 
/**
 * Class User
 * @package App\Models
 *
 * @property integer $__id
 * @property string $email
 * @property string $password
 * @property string|null $firstname
 * @property string|null $lastname
 * @property \DateTime|null $visited_at
 * @property string|null $phone
 * @property string|null $avatar
 * @property string|null $remember_token
 * @property \DateTime $created_at
 * @property \DateTime $updated_at
 */
class User extends Authenticatable
{
    use Notifiable;
 
    protected $primaryKey = '__id';
 
    public function roles() :BelongsToMany
    {
        return $this->belongsToMany(Role::class,'users_roles');
    }
}
 
Role.php:
<?php
 
namespace App\Models;
 
/**
 * Class Role
 * @package App\Models
 *
 * @property integer $__id
 * @property string $key
 * @property string $name
 */
class Role extends Model
{
    protected $primaryKey = '__id';
 
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class,'users_roles');
    }
}

UserController.php:

<?php
namespace App\Admin\Controllers;
 
class UserController extends AdminController
{
    protected $title = 'User';
 
    protected function grid()
    {
        $grid = new Grid(new User());
 
        $grid->column('__id', __('  id'));
        $grid->column('email', __('Email'));
        $grid->column('password', __('Password'));
        $grid->column('firstname', __('Firstname'));
        $grid->column('lastname', __('Lastname'));
 
        $grid->roles()->display(function($roles) {
 
            $roles = array_map(function ($role) {
                return "<span class='label label-success'>{$role['name']}</span>";
            }, $roles);
 
            return join('&nbsp;', $roles);
        });
        .......
         .........
}

Pivot table users_roles:

create table if not exists users_roles
(
    __user_id bigint not null
        constraint users_roles___user_id_foreign
            references users,
    __role_id bigint not null
        constraint users_roles___role_id_foreign
            references roles,
    become_at timestamp(0) not null,
    left_at timestamp(0)
);

I have noticed that name of column __role_id the pivot table is transformed in the query as role___id (as error message tells me: "ERROR: column users_roles.role___id"). How to solve this problem?

Answer 1

I had to make some correction:

 public function roles() :BelongsToMany
    {
      return $this->belongsToMany(Role::class,'users_roles',
          '__user_id','__role_id');
    }

and error message disappeared.