Level 55
@deringer, fixed! Well, for my part at least. Yes that is an issue.
1 like
Feb 10, 2015
46
Level 4
@mstnorris actually if you want to use your user object with navigation , it's better to go with view composer to avoid yourself repeatition
Level 55
@xroot what would this look like?
I have set up the view composer and I have reduced the number of queries by 2, however, some of the items in the navigation bar that I think I have passed through are still requiring their own query.
Level 4
@mstnorris could you share with us your view composer and the objects your trying to access from your navigation .
Level 55
@xroot sure,
Here is my navigation.blade.php partial
<nav id="navigation_bar" class="navbar navbar-default navbar-fixed-top" role="navigation">
<div class="container-fluid">
<div class="navbar-header hidden-xs">
<a class="navbar-brand" href="/dashboard">My Project</a>
</div>
<ul class="nav navbar-nav">
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-university"></i><span class="hidden-xs"> University <span class="caret"></span></span></a>
<ul class="dropdown-menu" role="menu">
<li><a href="#"><i class="fa fa-fw fa-mortar-board"></i> School <i class="fa fa-fw fa-arrow-circle-o-right pull-right"></i></a></li>
<li><a href="#"><i class="fa fa-fw fa-cubes"></i> Course <i class="fa fa-fw fa-arrow-circle-o-right pull-right"></i></a></li>
<li><a href="#"><i class="fa fa-fw fa-university"></i> University <i class="fa fa-fw fa-arrow-circle-o-right pull-right"></i></a></li>
</ul>
</li>
@if ( Auth::check() )
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-cube"></i><span class="hidden-xs"> My Modules <span class="caret"></span></span></a>
<ul class="dropdown-menu" role="menu">
@foreach ( $user->modules as $module )
<li><a href="#">{{ $module->module_code }} - {{ str_limit($module->name, 30) }}</a></li>
@endforeach
<li class="divider"></li>
<li><a href="#">View Last Years Modules</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-tasks"></i><span class="hidden-xs"> Tasks </span><span class="label label-danger">{{ $user->tasks()->count() }}</span></a>
<ul class="dropdown-menu" role="menu">
@foreach ( $user->tasks as $task )
<li><a href="#">{{ $task->text }}</a></li>
@endforeach
<li class="divider"></li>
<li><a href="#"><i class="fa fa-sign-out"></i> Clear All</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-inbox"></i><span class="hidden-xs"> Messages </span><span class="label label-danger">{{ $user->messages()->count() }}</span></a>
<ul class="dropdown-menu" role="menu">
@foreach ( $user->messages as $message )
<a href="#">
<li>
{{ $message->subject }}
<br />
</li>
</a>
@endforeach
<li class="divider"></li>
<li><a href="#"><i class="fa fa-sign-out"></i> Clear All</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-bell-o"></i><span class="hidden-xs"> Reminders </span><span class="label label-danger">{{ $user->reminders()->count() }}</span></a>
<ul class="dropdown-menu" role="menu">
@foreach ( $user->reminders as $reminder )
<li><a href="#">{{ $reminder->text }}</a></li>
@endforeach
<li class="divider"></li>
<li><a href="#"><i class="fa fa-sign-out"></i> Clear All</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-fa-pencil-square-o"></i><span class="hidden-xs"> Notes </span><span class="label label-danger">{{ $user->notes()->count() }}</span></a>
<ul class="dropdown-menu" role="menu">
@foreach ( $user->notes as $note )
<li><a href="#">{{ $note->text }}</a></li>
@endforeach
<li class="divider"></li>
<li><a href="#"><i class="fa fa-trash-o"></i> Add New</a></li>
<li class="divider"></li>
<li><a href="#"><i class="fa fa-trash-o"></i> Clear All</a></li>
</ul>
</li>
@endif
</ul>
<ul class="nav navbar-nav navbar-right">
@if (Auth::guest())
<li><a href="{{ route('login_path') }}">Log in</a></li>
<li><a href="{{ route('register_path') }}">Register</a></li>
@else
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"><i class="fa fa-user"></i><span class="hidden-xs"> {{ $user->name }}</span><span class="caret"></span></a>
<ul class="dropdown-menu" role="menu">
<li><a href="{{ route('dashboard_path') }}"><i class="fa fa-dashboard"></i> Dashboard</a></li>
<li><a href="{{ route('profile_path') }}"><i class="fa fa-user"></i> Profile</a></li>
<li><a href="#"><i class="fa fa-calendar"></i> Timetable</a></li>
<li><a href="#"><i class="fa fa-trophy"></i> <span class="hidden-xs"> Grades</span></a></li>
<li><a href="users"><i class="fa fa-users"></i> <span class="hidden-xs"> Classmates</span></a></li>
<li><a href="#"><i class="fa fa-cogs"></i> Settings</a></li>
<li><a href="#"><i class="fa fa-question"></i> Help</a></li>
<li class="divider"></li>
<li><a href="{{ route('logout_path') }}"><i class="fa fa-sign-out"></i> Log out</a></li>
</ul>
</li>
@endif
</ul>
</div>
</nav>
And here is my ViewComposerServiceProvider.php
<?php namespace App\Providers;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\ServiceProvider;
class ViewComposerServiceProvider extends ServiceProvider {
/**
* Bootstrap the application services.
*
* @return void
*/
public function boot()
{
$this->composeNavigation();
}
/**
* Register the application services.
*
* @return void
*/
public function register()
{
//
}
public function composeNavigation()
{
view()->composer('partials.navigation', function ($view) {
$view->with('user', Auth::user()->with('modules', 'tasks')->first());
});
}
}
Thank you
Level 56
What is the problem ?
Level 4
you should have notes and messages added to your query , so you can access its.
Level 55
@pmall the issue I am having is that I am still running about 21 queries, when I am eager loading the User model and its relations.
@xroot, I have added those. Am I right in thinking that a count(...) will always run another query to produce the badges within the nav? Is there anyway to do this with eager loading too?
Level 4
Level 55
@xroot I am still running 21 queries
This is all of them:
SELECT * FROM `users` WHERE `users`.`id` = '1' LIMIT 1 0.460 ms
SELECT * FROM `users` 0.360 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` in ('1', '2', '3', '4', '5', '6', '7') 0.610 ms
SELECT `permissions`.*, `permission_role`.`role_id` as `pivot_role_id`, `permission_role`.`permission_id` as `pivot_permission_id` FROM `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` WHERE `permission_role`.`role_id` in ('1', '2', '3', '4', '5') 0.910 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '1' 0.520 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '2' 0.280 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '3' 0.270 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '4' 0.260 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '5' 0.250 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '6' 0.280 ms
SELECT `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` FROM `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` WHERE `role_user`.`user_id` = '7' 0.260 ms
SELECT * FROM `users` LIMIT 1 0.210 ms
SELECT `modules`.*, `module_user`.`user_id` as `pivot_user_id`, `module_user`.`module_id` as `pivot_module_id` FROM `modules` inner join `module_user` on `modules`.`id` = `module_user`.`module_id` WHERE `module_user`.`user_id` in ('1') 0.410 ms
SELECT * FROM `tasks` WHERE `tasks`.`user_id` in ('1') 0.530 ms
SELECT * FROM `reminders` WHERE `reminders`.`user_id` in ('1') 0.280 ms
SELECT * FROM `notes` WHERE `notes`.`user_id` in ('1') 0.330 ms
SELECT * FROM `messages` WHERE `messages`.`sender_id` in ('1') 0.300 ms
SELECT count(*) as aggregate FROM `tasks` WHERE `tasks`.`user_id` = '1' 0.440 ms
SELECT count(*) as aggregate FROM `messages` WHERE `messages`.`sender_id` = '1' 0.270 ms
SELECT count(*) as aggregate FROM `reminders` WHERE `reminders`.`user_id` = '1' 0.280 ms
SELECT count(*) as aggregate FROM `notes` WHERE `notes`.`user_id` = '1' 0.280 ms
In your experience, can this be reduced at all?
Level 4
could you share with me the route you hit and the action linked to it within your controller .
Level 55
Is this what you are after?
class UsersController extends Controller {
public function index()
{
//$users = User::all(); // 28 queries
//$users = User::with('roles')->get(); // 22 queries
$users = User::with('roles.permissions')->get(); // 13 queries
return view('users.index', compact('users'));
}
}
I am hitting my-project.com/users
In my routes.php file
Route::resource('users', 'UsersController', ['middleware' => 'auth']);
Level 4
you can go with
$users = User::with('roles')->get();
the idea behind eager loading is that your relations are not loaded from the DB using the first query . for example lets imagine you did not loaded the roles within your query , when you do something like so in your view
@foreach($user->roles as $role)
{!! $role->name!!}
@endforeach
that means you're going to query your DB each time you loop throw , that what is considered as eager loading issue .but when you load your relations using with or load , that means your relations already in your collection you want hit the DB
so your query is fine for me.
Level 55
@xroot so just so I am clear and I understand, as I have it at the moment, it is as efficient as it can be?
Thank you for all your help and prompt replies.
Level 4
yes it is , one think to keep in mind when you're going to access your relations from your view just as you do now , always load the needed relations in your view, to avoid eager loading issue . your welcome
1 like
Level 56
@mstnorris man you have to learn when eloquent execute a query, we cant check all your code.
A query is executed each time you use find or first or get or all or count, etc, regardless if it was eager loaded or not.
$users = User::with(['posts'])->all();
$first_users_posts = $users[0]->posts()->get();
Above, three queries executed. One for all the users, one for all the posts, one for the posts of the first user. Because you explicitly told to execute the posts relationship of the first user by using get method on his posts relationship definition.
$users = User::with(['posts'])->all();
$first_users_posts = $users[0]->posts;
Above, two queries issued. One for all the users, one for all the posts. No query executed for the second line because you just retrieve the eager loaded collection of posts of the first user.
You have to understand the difference between $user->posts()>get() which execute a query every time regardless of eager loading and $user->posts which will automagically either execute the users posts query and return the collection of posts or directly return the collection of posts if it has been eager loaded.
Another thing which is misleading : query builder methods and collection methods often have the same name. For example $user->posts()->count() will execute a count query every time regardless of eager loading because you use the count method of the query builder of the users post query. However $user->posts->count() will not execute a query if the posts have been eager loaded because you are using the count method of the posts collection.
In your query log above it is easy to spot you have made a confusion like this because a query is eager loading the users roles and then one query is executed seven time to retrieve the role of each particular user. Fix this with what I explained in this post you can already get rid of seven queries.
You can also get rid of the last four count queries by using count() method on the user's relationships collections instead of using count on the relationship query builder. $user->messages->count() instead of $user->messages()->count() and so on.
Please or to participate in this conversation.