How can I use a joined variable in an eloquent query and manipulate it with Carbon to get the results I need

Question

Laravel "^9.0"

I am a bit stuck because the results of the query I am trying to build will solve a larger problem. Here is the situation: I have a system that stores the time of day to send a reminder to a user. The time isn't stored with anything else, but is stored in relation to their timezone. So, if a user is in the America/Los_Angeles timezone, and selects a daily reminder for 1pm, they should get that at 1pm. The server and application are all in UTC. Because I am not storing a timestamp (I don't need dates, just the time of day, I have found it almost impossible to build the reminder logic without significant queries (get all reminders first, parse to the user, get timezones, then sort by the time selected). I want to just query on the time while using the time zone of the user, through a simple JOIN, however, I can't seem to figure out how to use the users.timezone from the JOIN to make that work.

The reminders job runs every 5 min, users can only select reminders in 5 minute intervals (e.g. 5:45 pm (stored as 17:45 pm)). Here is the seemingly simple eloquent query:

$reminders = Reminder::join('users as users', 'users.id', '=', 'reminders.user_id')
        ->where('reminders.time', '>=', Carbon::now()->subMinutes(2)->setTimezone('users.timezone')->format('G:i:s'))
        ->where('reminders.time', '<=', Carbon::now()->addMinutes(2)->setTimezone('users.timezone')->format('G:i:s'))
        ->get();

Here is the error:

  Carbon\Exceptions\InvalidTimeZoneException 

  Unknown or bad timezone (users.timezone)

  at vendor/nesbot/carbon/src/Carbon/CarbonTimeZone.php:106
    102▕         }
    103▕ 
    104▕         if ($tz === false) {
    105▕             if (Carbon::isStrictModeEnabled()) {
  ➜ 106▕                 throw new InvalidTimeZoneException('Unknown or bad timezone ('.($objectDump ?: $object).')');
    107▕             }
    108▕ 
    109▕             return false;
    110▕         }

      +2 vendor frames 
  3   app/Console/Commands/SendReminders.php:48
      Carbon\Carbon::setTimezone("users.timezone")

      +13 vendor frames 
  17  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

Any help would be appreciated. Thank you.

Answer 1

So, it turns out what I was trying to do was impossible (even Taylor Otwell posted on Twitter - https://twitter.com/taylorotwell/status/1500100606274551816?s=21). So, I thought I would double back and show what I ended up doing:

First, when I stored the reminder, I stored it with the current users timezone and also as UTC, so I had the correct hour of the day to use for either. The complicated part here is just to get the offset correct, since I am not storing the full date, just the hour and minute.

Then in the job to send the reminders, I have this query (replacing the problem query above):

$results = Reminder::with('user')
        ->where('timeTz', Carbon::now()->format('H:i'))
        ->get();

I am using Carbon::now() since my app runs in UTC, and that is what the timeTz is stored as. I am also disregarding the seconds, and just looking at the hours and minutes.

So what would have been hundreds, if not thousands of queries (N+1, the original problem before the problem above), is now one.

I can't thank the PHP community on Twitter enough - some serious heavyweights came out and tried to help. Ultimately, the solution above made the most sense.