You should try
public function role()
{
return $this->belongsTo('App\Role');
}
in your User model.
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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.
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
Please or to participate in this conversation.