Level 37
User::with('dealer', 'role')->get()
2 likes
Jun 22, 2017
5
Level 10
Is there a better way to make this query?
Hello All,
I have a query that works as it is, but I'm thinking there could possibly be a better and more efficient way to run it. It suits it's purpose the way it is.
Please review and let me know if you think there is a way that is better.
Models:
// Dealer
public function users()
{
return $this->hasMany('Dbn\User');
}
// User
public function dealer()
{
return $this->belongsTo('Dbn\Dealer');
}
public function role()
{
return $this->belongsToMany('Dbn\Role');
}
// Role
public function users()
{
return $this->belongsToMany('Dbn\User');
}
// role_user - pivot table
user_id
role_id
// Tables:
dealers
users
roles
role_user
// DB query
Datatables::of(DB::table('users'))
->leftJoin('dealers', 'dealers.id', '=', 'users.dealer_id')
->leftJoin('role_user', 'role_user.user_id', '=', 'users.id')
->leftJoin('roles', 'roles.id', '=', 'role_user.role_id')
->select(DB::raw('role_user.role_id,roles.display_name,
dealers.name,users.id,users.prefix,users.first_name,users.last_name,
users.suffix,users.email,users.created_at'))
->where('users.deleted_at', null)
->make(true);
Not all users are related to a dealer. I tried join but will only return those that have a dealer_id and some DO NOT.
As I said it returns the results as expected just looking to clean it up a little if there is a way.
Thank you in advance for your assistance!
Regards,
Ray
Level 10
@Borisu - Thank you for that. I have converted it to the following and it is returning the results as expected but I cannot seem to output them to the datatables.
Here is my code for the columns:
columns: [
{ data: 'id', name: 'users.id' },
{ data: 'display_name', name: 'roles.display_name' },
{ data: 'first_name', name: 'users.first_name' },
{ data: 'last_name', name: 'users.last_name' },
{ data: 'email', name: 'users.email' },
{ data: 'name', name: 'dealers.name' },
{ data: 'created_at', name: 'users.created_at' };
]
And here is the updated query:
return Datatables::of(DB::table('users'))
->with('dealer', 'role')
->make(true);
When I dd the results it appears to have everything in it.
How do I get the proper columns in the right places. My error is: Column 'display_name', there is a column titled 'display_name'
It is also only displaying the users information and not the dealer or role information.
Thank you for your help.
Level 37
Shouldn't it be:
return Datatables::of(User::with('dealer', 'role'))->make(true);
More here: https://datatables.yajrabox.com/eloquent/relationships
Level 10
@Borisu you're right. Thank you for the help.
Still have some sorting issues but I think you've pointed me in the right direction. I've never really used with() in my queries. But after reading a little more and testing I see how to use it properly.
Thank you again.
Level 2
You should mark answers as correct once someone has provided a working solution ;)
Please or to participate in this conversation.