Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mstdmstd's avatar

Why 2 dates passed into whereRaw shows invalid sql tracement ?

In laravel 10 app need to path 2 dates parameters into whereRaw and I do it as :

     \Log::info(varDump($filterAtDate, ' -19 retrieveDbData $filterAtDate::'));
     $filterAtDateTill = (clone $filterAtDate->addDays(10));
     \Log::info(varDump($filterAtDateTill, ' -1 $filterAtDateTill::'));

         $this->reactions = Reaction
             ::query()->whereRaw('created_at BETWEEN ? AND ?', [ $filterAtDate, $filterAtDateTill ])
             ->groupBy('action')
             ->orderBy('reaction_count', 'desc')
             ->select(
                 $reactionTb . '.action',
                 DB::raw('count(' . $reactionTb . '.id) as reaction_count'))
             ->get()->toArray();
     }

Checking logs for these 2 vars I see valid dates :

[2024-05-24 09:35:22] local.INFO:  (Object of Carbon\Carbon) : -19 retrieveDbData $filterAtDate:: : Array
(
    [ * endOfTime] =>
    [ * startOfTime] =>
    [ * constructedObjectId] => 000000000000061a0000000000000000
    [ * localMonthsOverflow] =>
    [ * localYearsOverflow] =>
    [ * localStrictModeEnabled] =>
    [ * localHumanDiffOptions] =>
    [ * localToStringFormat] =>
    [ * localSerializer] =>
    [ * localMacros] =>
    [ * localGenericMacros] =>
    [ * localFormatFunction] =>
    [ * localTranslator] =>
    [ * dumpProperties] => Array
        (
            [0] => date
            [1] => timezone_type
            [2] => timezone
        )

    [ * dumpLocale] =>
    [ * dumpDateProperties] =>
    [date] => 2024-05-20 00:00:00.000000
    [timezone_type] => 3
    [timezone] => Europe/Kiev
)

[2024-05-24 09:35:22] local.INFO:  (Object of Carbon\Carbon) : -1 $filterAtDateTill:: : Array
(
    [ * endOfTime] =>
    [ * startOfTime] =>
    [ * constructedObjectId] => 00000000000006220000000000000000
    [ * localMonthsOverflow] =>
    [ * localYearsOverflow] =>
    [ * localStrictModeEnabled] =>
    [ * localHumanDiffOptions] =>
    [ * localToStringFormat] =>
    [ * localSerializer] =>
    [ * localMacros] =>
    [ * localGenericMacros] =>
    [ * localFormatFunction] =>
    [ * localTranslator] =>
    [ * dumpProperties] => Array
        (
            [0] => date
            [1] => timezone_type
            [2] => timezone
        )

    [ * dumpLocale] =>
    [ * dumpDateProperties] =>
    [date] => 2024-05-30 00:00:00.000000
    [timezone_type] => 3
    [timezone] => Europe/Kiev
)

But when I trace sql I see invalid sql, as 2 parameters are equal :

   SELECT `reactions`.`action`, count(reactions.id)     AS reaction_count
    FROM `reactions`
    WHERE created_at BETWEEN '2024-05-30 00:00:00'     AND '2024-05-30 00:00:00'
    GROUP BY `action`
    ORDER BY `reaction_count` desc

So 1st parameter was not passed and I used clone method to create a second var

Also I tried to use named parameters with whereRaw :

    ::query()->whereRaw('created_at BETWEEN :dateFrom AND :dateTill ', [ 'dateFrom' => $filterAtDate, 'dateTill' => $filterAtDateTill ])

But in this case tracing sql I see:

   SELECT `reactions`.`action`, count(reactions.id)     AS reaction_count
    FROM `reactions`
    WHERE created_at BETWEEN :dateFrom     AND :dateTill
    GROUP BY `action`
    ORDER BY `reaction_count` desc

How to make it correctly ?

0 likes
10 replies
s4muel's avatar

carbon is mutable, so need to clone it first. but not like you did:

$filterAtDateTill = (clone $filterAtDate->addDays(10));

but like this:

$filterAtDateTill = (clone $filterAtDate)->addDays(10); //mind the parenthesis
//or like this using the clone() method
$filterAtDateTill = $filterAtDate->clone()->addDays(10);
1 like
mstdmstd's avatar

@s4muel Yes, this way works ok. Could you please what in this case "carbon is mutable" means and your code works ?

s4muel's avatar
s4muel
Best Answer
Level 50

@mstdmstd when you do $filterAtDate->addDays(10) it changes the $filterAtDate value. you cloned the value (clone $filterAtDate->addDays(10)); (as from your question), but it was too late, it first added 10 days to original date and then it cloned it ending up both dates set to equal value. so you need to clone (copy) the date (make another carbon instance), before you change it (as in my example).

1 like
Snapey's avatar

Why not use whereBetween?

1 like
mstdmstd's avatar

@Snapey Looks like

     ::query()->whereRaw('created_at BETWEEN ? AND ?', [ $filterAtDate, $filterAtDate->clone()->addDays(1) ])

is the same as

       ::query()->whereBetween('created_at', [$filterAtDate, $filterAtDate->clone()->addDays(1)])

But second is prefered, as Raw is not good ?

Snapey's avatar

@mstdmstd using raw statements you have to use the sql syntax of your database which is not good for the portability of your application. If say you switched in the future to PGSQL your raw syntax might break.

Eloquent statements like whereBetween are written to be portable across database providers.

1 like

Please or to participate in this conversation.