Quadram
1 year ago

"is my pub currently open?". Problems using conditions in a query statement in Laravel

Posted 1 year ago by Quadram

Hi there!!

I'm trying the following:

I have two models: Pubs and Schedules (where I store statically the pubs schedules). They 're related one to each other as follows:

Pub:


public function pubSchedules()
{
       return $this->hasMany( Schedule::class );
}

Schedule:


public function pub()
{
        return $this->belongsTo(Pub::class); 
}

Table schedules has the following fields:

id| pub_id | week_day | opening_time | closing_time

I'm developing a filter that shows only open pubs in current moment.

The first function I made works perfectly:


public static function isPubCurrentlyOpen($pubs)
{
     $pubs->whereHas('pubSchedules', function ($pubs) {
         $pubs->where('week_day', Carbon::now()->dayOfWeek)
             ->whereRaw(
                 "'" . Carbon::now('Europe/Madrid')->format("H:i:s") .
                   "' BETWEEN opening_time AND closing_time
                 "
             );
       } );
}

It returns a query that can join with other filters.

The problem is when I have a pub with this kind of schedule: 09:00 - 03:00 in the same day. Of course 03:00 is in the following day, but we chose this possibility to make easier for the client to store the schedules.

Well, in this example it makes that "Carbon::now('Europe/Madrid')->format("H:i:s")" is not between opening_time and closing_time, so this pub it's not going to appear as opened (when it is).

Making some tests on MySql I tried something like this:


SELECT *,
CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day`, ' ', `opening_time`) AS `opening_datetime`,
IF(`closing_time` < `opening_time`, 
CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day` + 1, ' ', `closing_time`),
CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day`, ' ', `closing_time`)
) AS `closing_datetime`
FROM tappear_borrador.schedules;

It works perfectly setting "one more day" to the closing_time when it's greater than 00:00, but I'm having problems to set something like thar in my function:


$pubs->whereHas('pubSchedules', function ($pubs) {
        $pubs->where('week_day', Carbon::now()->dayOfWeek)
             ->whereRaw(
              "'" . Carbon::now('Europe/Madrid')->format('Y-m-d H:i:s') .
                 "' BETWEEN CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', ".Carbon::now()->dayOfWeek.", ' ', opening_time)
                  AND IF(closing_time < opening_time,
                          CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', ".Carbon::now()->dayOfWeek." + 1, 
                ' ', closing_time),
                          CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', ".Carbon::now()->dayOfWeek.", ' ', 
                closing_time)
                  ) 
                    "
            );
} );

This I made it's not returning anything (any error too). ((I also checked that json_die(Carbon::now()->dayOfWeek+1) doesn't return one more value to current day...may it be one of the problems?)),

Check that changed current moment to compare years/month/h:i:s with opening and closing times.

So friends: I need your help to improve my last code to make if closing_time < opening_time, closing_time will be in the following day and then compare if current moment is/not between.

I'd like to make something like this:


public static function isPubCurrentlyOpen($pubs)
{
        $pubs->whereHas('pubSchedules', function ($pubs) {
           $pubs->where('week_day', Carbon::now()->dayOfWeek)
               ->whereRaw(
                    "'" . Carbon::now('Europe/Madrid')->format("H:i:s") .
                        "' BETWEEN openingDateTime AND closingDateTime
                       "
                );
        } );
}

where:


openingDateTime = " CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day`, ' ',       `opening_time`)  ";

closingDateTime = "IF(`closing_time` < `opening_time`,
      CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day` + 1, ' ', 
      `closing_time`),
      CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', `week_day`, ' ', 
      `closing_time`)
)" ;

Any ideas? Thanks a lot!!

Please sign in or create an account to participate in this conversation.