Error using carbon in a query

Question

I have a problem using if conditions in mysql

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:

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function pubSchedules()
{
    return $this->hasMany( Schedule::class );
}

Schedule:

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
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 it 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 .

Any ideas? Thanks a lot