Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

LaraBABA's avatar

Confused about User and Role with Eloquent

Hello,

I am making an API with Laravel, everything worked great with all my models and controllers until I tried to pull the users and roles from the users and roles tables with eloquent.

The error I receive is:

Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.role_id' in 'where clause' (SQL: select * from `roles` where `roles`.`role_id` in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)) in file C:\laragon\www\website\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 664

My user Model is:

<?php

namespace App;

use Laravel\Passport\HasApiTokens;
use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;

/**
 * Class User
 * @package App
 */
class User extends Authenticatable
{
    use HasApiTokens, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'surname', 'email', 'password', 'business', 'address1', 'address2', 'postcode', 'country', 'phone', 'vat', 'newsletter', 'role_id'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasOne
     */
    public function role()
    {
        return $this->hasOne('App\Role', 'role_id');
    }

}

My role model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Role extends Model
{
    protected $table = 'roles';

    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function user()
    {
        return $this->hasMany('App\User');
    }
}

My query(in the userController:

<?php

namespace App\Http\Controllers\API;

use App\User;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * @return \Illuminate\Http\JsonResponse
     */
    public function index()
    {
        $fetchUsers = User::with('role')->get();  <----------------------

        if ($fetchUsers) {
            return response()->json(['data' => $fetchUsers], 200);
        } else {
            return response()->json(['message' => Lang::get('no-result-to-show')], 200);
        }
    }
}

Any idea why this query does not work please?

I used the exact same query and relations between my blog and blog posts and it works but when it comes to pull all the users + their role names at the same time via the API, I cannot.

But I can pull the users or the roles separately.

Thank you for your help.

0 likes
6 replies
ouhare's avatar

You should try

public function role()
{
        return $this->belongsTo('App\Role');
}

in your User model.

Snapey's avatar

Assuming user has one Role then @ouhare answer is correct

Any time you have a foreign key on a table, you are using a belongsTo relationship

If a user can have many roles then you need a pivot table, and remove role_id column from users table

You should also rename this relationship as it will cause you problems in the future

    public function user()
    {
        return $this->hasMany('App\User');
    }

as a Role has many users then the relationship should be named plurally.

    public function users()
    {
        return $this->hasMany('App\User');
    }

This is not the cause of your present problem

LaraBABA's avatar

Thank you so much to everyone. I understand now As I like to design my table relations with workbench, do you think I can trust this tool to create all my models and relations please? It will help me greatly. https://github.com/Xethron/migrations-generator

I am using Laravel 5.7.

Thank you.

Snapey's avatar
Snapey
Best Answer
Level 122

No, I don't think so.

You are still needing to know if you have a foreign key on table A pointing to table B or B pointing to A, or if you need a pivot table.

Best tool is to write in words what relationships you need

ie,

User belongs to a Role / Role has many users

Post belongs to many Tag / Tag belongs to many posts

and then from this work out which table gets a FK

This might help also

https://laracasts.com/discuss/channels/eloquent/remember-which-table-gets-foreign-key-in-relationship

LaraBABA's avatar

@SNAPEY - Thanks for that, great way of doing it :-) I like it. I will do that from now on

Please or to participate in this conversation.