reinink / laracon2018 Goto Github PK
View Code? Open in Web Editor NEWSource code for my example app used in my Laracon Online 2018 talk
Home Page: https://laracon.net/
Source code for my example app used in my Laracon Online 2018 talk
Home Page: https://laracon.net/
it will be great to add links to commits in ;)
Hi Jonathan,
I "attended" your talk on eager loading in laravel at this year's online laracon. We're building our first app with laravel and it was very helpful as we are starting to see the same n+1 issues you were describing.
I'm struggling with a couple more advanced areas and I'm hoping you'd be willing to provide some guidance as I haven't been able to find much through my normal routine of googling.
I have two main questions.
When you're constraining eager loads I am assuming when you access that eager loaded property later it will access the constrained version. For example, we have this snippet of code:
// query every sent message
$query = Message::sent()->whereIn('id', $msgIDs)->with(['sentTo' => function ($q) {
$q->withTrashed();
}]);
Before using the eager loading we would have accessed that property like this:
$msg->sentTo()->withTrashed()->get()
but after we can just do:
$msg->sentTo
and the framework will know that property was eager loaded and use what's there (which should be constrained to include the soft-deleted records) correct?
public function getContactNameAttribute() {
if ($this->type == "person") {
$name = $this->user->full_name;
} else {
$name = $this->name;
}
return $name;
}
So every time we're using the contact_name attribute it runs at least one query, and sometimes two if it needs to access the $this->user->full_name (User is another model).
Is there any way to eager load those custom attributes?
Again, appreciate the time you took putting the talk together - we found it really useful.
Hello Jonathan,
I have a question that I would like you to share your thoughts about.
The question is about self relations and eager loading.
I have a table migration with a relation to itself as follows:
Schema::create('employees', function (Blueprint $table) {
$table->bigIncrements('id');
$table->unsignedBigInteger('employee_id')->nullable();
$table->string('name');
...
});
And in my Employee model I have a relation that returns subordinates for a given employee
public function subordinates(): HasMany
{
return $this->hasMany(Employee::class, 'employee_id');
}
Imagine the following data:
ID | employee_id | name |
---|---|---|
1 | null | John Doe |
2 | 1 | Jane Doe |
3 | 2 | Felipe Dalcin |
The issue is that I need to retrieve subordinates recursively. When trying to return all John's subordinates it should return
[
{"id": 2, "name": "Jane Doe"},
{"id": 3, "Felipe Dalcin"}
]
Because all Jane's subordinates are also John's.
The way it is designed right now will cause a n + 1 problem.
Is there a nicer way to avoid this and return it in a simpler way? Would you recommend a better solution?
Thanks in advance
After introducing the example "last login" to my project, a queued job fails when the database driver is being used. It works when using the sync driver.
Snippet from my user model:
protected static function boot()
{
parent::boot();
static::addGlobalScope('lastLogin', function ($query) {
$query->withLastLoginLink();
});
}
public function scopeWithLastLoginLink($query)
{
$query->addSubSelect('last_login_id', Login::select('id')
->whereColumn('user_id', 'users.id')
->latest()
)->with('lastLoginLink');
}
public function lastLoginLink()
{
return $this->belongsTo(Login::class);
}
My function that dispatches the job:
protected function notifyOfNewComment($comment)
{
$users = $this->subscriptions
->where('user_id', '!=', $comment->user_id)->pluck('user_id');
foreach ($users as $userid) {
dispatch(new SendNewCommentEmail($this, $userid, $comment));
}
if ($comment->rec_can_see) {
dispatch(new SendNewCommentEmail($this, $this->owner->id, $comment));
}
}
The job:
class SendNewCommentEmail implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
protected $gift;
protected $user;
protected $comment;
public $tries = 5;
/**
* Create a new message instance.
*
* @return void
*/
public function __construct($gift, $user, $comment)
{
$this->gift = $gift;
$this->user = User::getUser($user);
$this->comment = $comment;
}
/**
* Execute the job.
*
* @return void
*/
public function handle(Mailer $mailer)
{
if ($this->user->id != $this->gift->user_id) {
$mailer->to($this->user->email)->send(new NewComment($this->gift, $this->comment));
} else {
$mailer->to($this->user->email)->send(new MyNewComment($this->gift, $this->comment));
}
}
}
And the error I get:
Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'integer_in_raw' in 'where clause' (SQL: select * from users where integer_in_raw = users.id)
Job class: App\Jobs\SendNewCommentEmail
Job body: {"displayName":"App\Jobs\SendNewCommentEmail","job":"Illuminate\Queue\CallQueuedHandler@call","maxTries":5,"timeout":null,"timeoutAt":null,"data":{"commandName":"App\Jobs\SendNewCommentEmail","command":"O:28:\"App\Jobs\SendNewCommentEmail\":11:{s:7:\"\u0000\u0000gift\";O:45:\"Illuminate\Contracts\Database\ModelIdentifier\":4:{s:5:\"class\";s:8:\"App\Gift\";s:2:\"id\";i:22;s:9:\"relations\";a:3:{i:0;s:5:\"owner\";i:1;s:19:\"owner.lastLoginLink\";i:2;s:13:\"subscriptions\";}s:10:\"connection\";s:5:\"mysql\";}s:7:\"\u0000\u0000user\";O:45:\"Illuminate\Contracts\Database\ModelIdentifier\":4:{s:5:\"class\";s:8:\"App\User\";s:2:\"id\";i:1;s:9:\"relations\";a:0:{}s:10:\"connection\";s:5:\"mysql\";}s:10:\"\u0000\u0000comment\";O:45:\"Illuminate\Contracts\Database\ModelIdentifier\":4:{s:5:\"class\";s:11:\"App\Comment\";s:2:\"id\";i:457;s:9:\"relations\";a:1:{i:0;s:4:\"gift\";}s:10:\"connection\";s:5:\"mysql\";}s:5:\"tries\";i:5;s:6:\"\u0000\u0000job\";N;s:10:\"connection\";N;s:5:\"queue\";N;s:15:\"chainConnection\";N;s:10:\"chainQueue\";N;s:5:\"delay\";N;s:7:\"chained\";a:0:{}}"}}
Exception:
#0 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select from `...', Array, Object(Closure))
#1 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select from ...', Array, Object(Closure))
#2 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2027): Illuminate\Database\Connection->select('select * from...', Array, true)
#3 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2015): Illuminate\Database\Query\Builder->runSelect()
#4 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2499): Illuminate\Database\Query\Builder->Illuminate\Database\Query{closure}()
#5 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2016): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(516): Illuminate\Database\Query\Builder->get(Array)
#7 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(500): Illuminate\Database\Eloquent\Builder->getModels(Array)
#8 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(155): Illuminate\Database\Eloquent\Builder->get(Array)
#9 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(144): Illuminate\Database\Eloquent\Relations\Relation->get()
#10 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(564): Illuminate\Database\Eloquent\Relations\Relation->getEager()
#11 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(533): Illuminate\Database\Eloquent\Builder->eagerLoadRelation(Array, 'owner', Object(Closure))
#12 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(490): Illuminate\Database\Eloquent\Builder->eagerLoadRelations(Array)
#13 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/SerializesAndRestoresModelIdentifiers.php(85): Illuminate\Database\Eloquent\Model->load(Array)
#14 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/SerializesAndRestoresModelIdentifiers.php(55): App\Jobs\SendNewCommentEmail->restoreModel(Object(Illuminate\Contracts\Database\ModelIdentifier))
#15 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/SerializesModels.php(45): App\Jobs\SendNewCommentEmail->getRestoredPropertyValue(Object(Illuminate\Contracts\Database\ModelIdentifier))
#16 [internal function]: App\Jobs\SendNewCommentEmail->__wakeup()
#17 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(42): unserialize('O:28:"App\Jobs\...')
#18 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(83): Illuminate\Queue\CallQueuedHandler->call(Object(Illuminate\Queue\Jobs\DatabaseJob), Array)
#19 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(327): Illuminate\Queue\Jobs\Job->fire()
#20 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(277): Illuminate\Queue\Worker->process('database', Object(Illuminate\Queue\Jobs\DatabaseJob), Object(Illuminate\Queue\WorkerOptions))
#21 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(118): Illuminate\Queue\Worker->runJob(Object(Illuminate\Queue\Jobs\DatabaseJob), 'database', Object(Illuminate\Queue\WorkerOptions))
#22 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(102): Illuminate\Queue\Worker->daemon('database', 'default', Object(Illuminate\Queue\WorkerOptions))
#23 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(86): Illuminate\Queue\Console\WorkCommand->runWorker('database', 'default')
#24 [internal function]: Illuminate\Queue\Console\WorkCommand->handle()
#25 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(29): call_user_func_array(Array, Array)
#26 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container{closure}()
#27 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(31): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure))
#28 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Container/Container.php(572): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL)
#29 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Console/Command.php(183): Illuminate\Container\Container->call(Array)
#30 /var/www/html/dev.test/public_html/vendor/symfony/console/Command/Command.php(255): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#31 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Console/Command.php(170): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#32 /var/www/html/dev.test/public_html/vendor/symfony/console/Application.php(886): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#33 /var/www/html/dev.test/public_html/vendor/symfony/console/Application.php(262): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Queue\Console\WorkCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#34 /var/www/html/dev.test/public_html/vendor/symfony/console/Application.php(145): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#35 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Console/Application.php(89): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#36 /var/www/html/dev.test/public_html/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(122): Illuminate\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#37 /var/www/html/dev.test/public_html/artisan(37): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#38 {main}
I cannot figure out where the 'integer_in_raw' column it is complaining about is coming from. It doesn't have the problem when the queue driver is "sync"!
I cloned the project and am getting an error when running the database seeder:
php artisan db:seed
In Connection.php line 664:
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2
011-03-13 02:27:30' for column 'created_at' at row 1 (SQL: insert into `int
eractions` (`type`, `created_at`, `updated_at`, `customer_id`) values (Brea
kfast, 2011-03-13 02:27:30, 2011-03-13 02:27:30, 37))
In Connection.php line 458:
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2
011-03-13 02:27:30' for column 'created_at' at row 1
Is there something that I need to change in order to make the date comparisons work?
Thank you for the talk and project code
I have one question though, that could be more of a philosophical question.
In this application I am recommending a product (which exists in a table within the database) based the results of a "quiz" that the user takes (also exists within the database). Within the user's dashboard there could be 30, 40 or even more quizzes; all requiring the name of the recommended product to be visible. The catch is, I am not referring to these products by a foreign key. I am building a search query based on the results that the user inputs during the quiz. See below:
$recommendedProducts = Product::where('location', 'LIKE', '%'.$location.'%')
->whereIn('installed_in_traffic_area', $installationInTrafficDef)
->where('flow_rate', '>=', $flowRate)
->where('grease_capacity', '>=', $pumpCycleCapacity)->get();
Is there a way to cut down on my database queries using scopes, eager loading, or whatever, to retrieve the related product without having to query my database for every single quiz? My initial thought was no, but after seeing/hearing your talk I am now wondering if my initial thought was incorrect.
Hello Jonathan,
Following your reply to the email I wrote you, Here's the problem I'm facing in more detail:
Given an events
table that has begins_at
, ends_at
and user_id
fields, the query I want to run should return the users (plus a relationship on the users table) that have at least an event in a given hour. The problem is that I need to run this query for every hour in a day. Totaling 24 queries.
What I came up with so far is the following query scope on the User
model:
public function scopeWithEventsBetween($query, Date $beginning, Date $ending)
{
return $query->whereHas('events', function ($query) use ($beginning, $ending) {
$query->between($beginning, $ending);
});
}
and this scope on the Event
model:
public function scopeBetween($query, Date $beginning, Date $ending)
{
return $query->where(function ($query) use ($beginning, $ending) {
// ENDS IN THE INTERVAL
$query->where($this->getTable() .'.ends_at', '>', $beginning)->where($this->getTable() . '.ends_at', '<=', $ending);
})->orWhere(function ($query) use ($beginning, $ending) {
// BEGINS IN THE INTERVAL
$query->where($this->getTable() . '.begins_at', '>=', $beginning)->where($this->getTable() . '.begins_at', '<', $ending);
})->orWhere(function ($query) use ($beginning, $ending) {
// BEGINS AND ENDS OUTSIDE THE INTERVAL
$query->where($this->getTable() . '.begins_at', '<', $beginning)->where($this->getTable() . '.ends_at', '>', $ending);
});
}
The code to get the users with event in the next 24 hours is the following:
$situation = collect();
$beginning = Date::today();
$ending = Date::today()->addDay();
for ($day = $beginning->copy(); $day->lt($ending); $day->addHour()) {
$usersForHour = User::with('skills')->withEventsBetween(
$day->copy(),
$day->copy()->addHour(),
)->get();
$situation->push($usersForHour);
}
I've tried to simplify the problem as much as possible but on my production database this fairly simple query makes the CPU jump to 100% (only caused by mysql) and the page averages 12 seconds to load (sometimes even 20s depending on the number of events). This makes it impossible to increase the time resolution to 30mins or 15mins as the number of queries would double or quadruple and the page load time would skyrocket.
So I wanted to know if you might help me figuring out a way to fetch the users in a more performant way. I'm fine even with just the user ids being fetched.
For the sake of being precise the actual query scope I'm running is the following:
public function scopeSituationBetween($query, Date $beginning, Date $ending)
{
// has a shift but is not absent
// present
// substituting someone
return $query->where(function ($query) use ($beginning, $ending) {
$query->whereHas('shifts', function ($query) use ($beginning, $ending) {
$query->between($beginning, $ending);
})->whereDoesntHave('availabilities', function ($query) use ($beginning, $ending) {
$query->between($beginning, $ending)->whereStatus('unavailable');
});
})->orWhere(function ($query) use ($beginning, $ending) {
$query->whereHas('availabilities', function ($query) use ($beginning, $ending) {
$query->between($beginning, $ending)->whereStatus('available');
});
})->orWhere(function ($query) use ($beginning, $ending) {
$query->whereHas('shiftSubstitutions', function ($query) use ($beginning, $ending) {
$query->between($beginning, $ending);
});
});
}
Thank you for your time.
Luca
Hi Jonathan
Firstly, fantastic work here - it's really taken my queries to the next level, so thank you.
After using these methods 'in anger', one thing that did have me scratching my head for a good 20 minutes though, is that when you're passing Eloquent Building instances to the addSubSelect
macro, the getQuery
method on the builder returns the underlying query without any applicable model scopes applied. This resulted in me getting some unexpected results as I had a number of global scopes being enforced..
After taking a look at the Eloquent Builder class, a toBase
method exists which applies the scopes to the underlying query, so this can be used for in place of getQuery
when an Eloquent Query Builder is passed to our macro instead. This then ensures any scopes are applied before being parsed to a sub-select ๐
Builder::macro('addSubSelect', function ($query, $column) {
if (is_null($this->getQuery()->columns)) {
$this->select($this->getQuery()->from.'.*');
}
if ($query instanceof self) {
return $this->selectSub($query->limit(1)->toBase(), $column);
}
return $this->selectSub($query->limit(1)->getQuery(), $column);
});
Happy to PR if you think it would be of benefit, but wanted to share nonetheless.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.