How can I use a joined variable in an eloquent query and manipulate it with Carbon to get the result-CodePudding

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.

CodePudding user response：

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.