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

Answer 1

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.