Boubou
6 months ago

Confused about User and Role with Eloquent

Posted 6 months ago by Boubou

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.

Please sign in or create an account to participate in this conversation.