I would like to optimize my query but I am not sure where to start. I have this:
DB::transaction(static function () {
$array = DB::table('orders') // First step look for expired orders
->with('user')
->where('created_at', '<=', now()->subMonth()->toDateTimeString())
->where('notifications', '=', 0)
->toArray();
$emails = Arr::pluck($array, 'email'); //The email column is part of the users table
foreach ($emails as $email)
{
Mail::to($email)->send(new NotifyUsersOfExpiredService()); // Second step, Send jobs to queue
DB::table('orders') // Set each order to 1 (meaning that the notification has been sent)
->with('user')
->where('email', '=', $email) //The email column is part of the users table
->update(['notifications' => 1]);
}
});
With your experience, how would you have built my query please?
I am using a transaction as I am worried that the notifications fields get set to 1 even if no emails are sent.
I am not sure if this is the way to go....what do you think?
here it can be, you no need to add order table again in updating query because users emails are unique
DB::transaction(static function () {
$array = DB::table('orders') // First step look for expired orders
->with('user')
->where('created_at', '<=', now()->subMonth()->toDateTimeString())
->where('notifications', 0)
->toArray();
$emails = Arr::pluck($array, 'email'); //The email column is part of the users table
foreach ($emails as $email) {
Mail::to($email)->send(new NotifyUsersOfExpiredService()); // Second step, Send jobs to queue
DB::table('user')->where('email', $email) //The email column is part of the users table
->update(['notifications' => 1]);
}
});
Thank you so much for the reply.
I am wondering....perhaps I should email each person right away from here rather than adding them into a queue because this will be triggered via cron job..... no?
For me the queue/jobs idea is to run the script asynchronously but if done via cron job, it is the same no?
To my surprise, ->with('user') does not work with DB, only with eloquent/model query.
And it looks also like transactions only work for DB queries and not Eloquent Model.
I wonder why....
I think I will have to use a ->join() instead
