Illuminate database query with date_sub

Question

I'm struggling with a query using the Illuminate database query builder. When I use the query the result is not as I expected. When using the query from the querylog directly with mysql cli, I get the expected result.

With query builder:

    ->table('CompanyTools')
    ->select(
        'CompanyTools.toolId', 
        $db->raw('COUNT(CompanyTools.toolId) as count')
    )
    ->whereYear('CompanyTools.date', '>', 'YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))')
    ->groupBy('CompanyTools.toolId')
    ->orderBy('count', 'DESC')
    ->take(1)
    ->get();

Result:

Array ( [toolId] => 88 [count] => 55 )

With mysql cli:

select `CompanyTools`.`toolId`, COUNT(CompanyTools.toolId) as count from `CompanyTools` 
where year(`CompanyTools`.`date`) > YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) 
group by `CompanyTools`.`toolId` 
order by `count` desc 
limit 1

Result:

ToolId: 88
count: 17

If I (in the query builder) replace 'YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))'with 2013 I get:

Array ( [toolId] => 88 [count] => 17 )

Somehow the date_sub get ignored so the result includes all years

I tried with ->whereYear('CompanyTools.date', '>', $db->raw('YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))')) without any luck.

I guess I could use php to calculate the desired year, but I would rather get the query right.

Thx in advance

/ j

UPDATE

Replacing

->whereYear('CompanyTools.date', '>', 'YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))')

with

->where($db->raw('YEAR(CompanyTools.date)'), '>', $db->raw('YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))'))

solves it. Not clever enough to figure out why, but perhaps the whereYear function is supposed to be used diffently

Try `->toSql()` instead of `->get()` and check the SQL string that's returned. Maybe you'll spot the mistake. — lukasgeiter, Dec 28 '14 at 13:09
That outputs `select CompanyTools.toolId, COUNT(CompanyTools.toolId) as count from CompanyTools where year(CompanyTools.date) > YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) group by CompanyTools.toolId order by count desc limit 1` which is the query I use in mysql cli — sjosen, Dec 28 '14 at 13:13
Weird. And if you run the query Laravel created with the CLI it returns the correct result? — lukasgeiter, Dec 28 '14 at 13:18
Exactly. Only difference I can tell is that the cli doesn't use bindings. — sjosen, Dec 28 '14 at 13:28
Just to be sure, you execute the two queries on the same database do you? — lukasgeiter, Dec 28 '14 at 13:29
Yes. I use the builder several times with other queries and this is the only one bugging me. — sjosen, Dec 28 '14 at 13:39
Try running the whole query with `DB::select('select CompanyTools....')` — lukasgeiter, Dec 28 '14 at 13:45
That gives me `Array ( [0] => Array ( [toolId] => 88 [count] => 17 ) )` which is the expected result — sjosen, Dec 28 '14 at 14:10

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

As you already found out using

->where($db->raw('YEAR(CompanyTools.date)'), '>', $db->raw('YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))'))

Or alternatively

->whereRaw('YEAR(CompanyTools.date) > YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))')

solves the problem.

But why is that?

For every "normal" query, Laravel uses bindings. Obviously SQL functions like YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) don't work with bindings.

Normally, you can use DB::raw('YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))') and the Laravel won't use bindings. For example in where() (Expression is the class DB::raw() returns)

if ( ! $value instanceof Expression)
{
    $this->addBinding($value, 'where');
}

But the whereYear() function doesn't do such a thing. It uses addDateBasedWhere() and just adds a binding without checking if the value is an instance of Expression

protected function addDateBasedWhere($type, $column, $operator, $value, $boolean = 'and')
{
    $this->wheres[] = compact('column', 'type', 'boolean', 'operator', 'value');

    $this->addBinding($value, 'where');

    return $this;
}

This means the query will use bindings and therefore NOT execute the date calculation at all.

Illuminate database query with date_sub

1 Answers1

But why is that?