[5.6] whereYear criteria type issue

[vpratfr]

• Laravel Version: 5.6.18
• PHP Version: 7.2
• Database Driver & Version: sqlite as bundled with latest Homestead

---

Description:

Since #16737 is closed, I am opening this issue. I ran into the same issue when writing a query scope and unit testing it

I've database table of orders. I fetch the orders with whereYear. If the year argument is a integer it wont find any orders but if I typecast the year to a string it works.

If it helps to reproduce, I am using Homestead to run my unit tests. Tests are executed remotely on the VM using PHPStorm. I use a Windows OS to develop.

Steps To Reproduce:

This wont work since the year is an integer.

Order::whereYear('created_at', Carbon::now()->year)->get();

This will work since the year is a string.

Order::whereYear('created_at', (string) Carbon::now()->year)->get();

[staudenmeir]

I'm not sure this is considered a bug, the documentation uses quotes:

$users = DB::table('users')
    ->whereYear('created_at', '2016')
    ->get();

But I agree that it would be nice to have, since integers work with MySQL.

[sisve]

It may be easier to fix if you can provide a unit test that currently fails.

[vpratfr]

Here it is. The following test case fails on the last assertion.

<?php
// database/migrations/2018_05_01_073658_create_dummies_table.php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateDummiesTable extends Migration
{
    public function up()
    {
        Schema::create('dummies', function (Blueprint $table) {
            $table->increments('id');
            $table->date('expires_at');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('dummies');
    }
}

And the model + test

<?php
// Unit/WhereYearTest.php

namespace Tests\Unit;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Foundation\Testing\DatabaseMigrations;
use Tests\TestCase;

class Dummy extends Model
{
    protected $fillable = ['expires_at'];
    protected $dates = ['expires_at'];
}

class WhereYearTest extends TestCase
{
    use DatabaseMigrations;

    /** @test */
    public function can_query_with_int_year()
    {
        Dummy::create(['expires_at' => Carbon::create(2013, 10, 12)]);
        Dummy::create(['expires_at' => Carbon::create(2012, 10, 12)]);
        Dummy::create(['expires_at' => Carbon::create(2013, 10, 12)]);
        Dummy::create(['expires_at' => Carbon::create(2011, 10, 12)]);

        $this->assertCount(4, Dummy::get());
        $this->assertCount(2, Dummy::whereYear('expires_at', '2013')->get(), 'Failed to query with string year');
        $this->assertCount(2, Dummy::whereYear('expires_at', 2013)->get(), 'Failed to query with int year');
    }
}

Test output:

Failed to query with int year
Failed asserting that actual size 0 matches expected size 2.
 /home/vagrant/code/tests/Unit/WhereYearTest.php:30

[vpratfr]

One more precision:

The test is passing when executed on the Homestead MySQL database. It is failing when executed on the test SQLite :in_memory: database.

Query generated on SQLite:

"select * from "dummies" where strftime('%Y', "expires_at") = ?"

Query generated on MySQL:

"select * from `dummies` where year(`expires_at`) = ?"

So I guess that query is handled differently at the DB driver level. There may be a need to make the query more coherent by:

• always casting the year parameter to a string at eloquent level?
• updating the driver in order to cast the parameter to a string in such cases
• updating the driver to change the generated query to take that into account

There may be a need to check behaviour for whereMonth and whereDay which probably is the same.

[staudenmeir]

We could use this in SQLiteGrammar::dateBasedWhere():

return "strftime('{$type}', {$this->wrap($where['column'])}) {$where['operator']} cast({$value} as text)";

whereDay() and whereMonth() are a bit different, since they use leading zeros (01).