Filter records by month and year in Laravel

Question

I have a form with a field like month, that sends a value in the format Y-m for my controller. How do I search the records for the selected month and year?

I’m using L5-Repository

I did it this way, but it doesn’t work:

$date = Input::get('date');
$type = Input::get('type');

$values = $this->repository->findWhere(['type' => $type, 'date' => $date]);

From the following error:

Queryexception in Connection.php line 761: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type date: "2016-09" (SQL: select * from "values" Where "type" = Recipe and "date" = 2016-09)

I thought I was going to make that mistake, but I don’t know how to make it work. Someone gives a light?

I’m using Postgres database and the field date is in format date even.

Answer 1

Being a field of sorts date, you can format the date before comparison. In your repository create a method similar to this

// Em MySQL, por usar uma raw
public function findByBatata($type, $date, $format = '%Y-%m') {
    return $this->model
         ->where(\DB::raw("DATE_FORMAT(`date`, '$format')", '=', $date))
         ->where('type', '=', $type)
         ->get();
}

// Em Postgresql
public function findByBatata($type, $date, $format = 'YYYY-MM') {
    return $this->model
         ->where(\DB::raw("to_char(`date`, '$format')", '=', $date))
         ->where('type', '=', $type)
         ->get();
}

This may not be very performatic because the indexes in this field will not be considered.

As pointed out in another answer, it is possible to use a crease of dates. Eloquent has the method whereBetween for that reason:

public function findByBatata($type, $date, $format = 'Y-m') {
    // Considerando data no formato 2016-02
    $dateTime = \DateTime::createFromFormat($format, $date);

    // o primeiro dia do mês hard coded
    $dateStart = $dateTime->format('Y-m-01');

    // t é equivalente ao último dia do mês
    $dateEnd = $dateTime->format('Y-m-t');

    return $this->model
         ->whereBetween('date', [$dateStart, $dateEnd])
         ->where('type', '=', $type)
         ->get();
}

Another option is to duplicate this already format field in month and year format, insert an index and search directly for this new field.

Answer 2

And how is the date stored? Why should the same error also occur in the correct INSERT? How is the database modeling? The field date is of the DATE or VARCHAR type?

I believe in two possibilities.

1 - Search with a range beginning and end of the month, but that depends on how the data is in the database.

$date = date('Y-m'); //so exemplo para ficar o ano e mes atual

//precisa adicioanr o range dos dias
$dateStart = date($date.'-01');
$dateEnd = date($date.'-t'); //o t pega o maximo de dias do mes

//ai é so fazer a busca pelo intervalo do mes com base nos daias
echo 'SELECT * FROM "values" WHERE "type" = "Receita" AND (date >= '.$dateStart.' AND date <= '.$dateEnd.' )';

The result will be something like

SELECT * FROM "values" WHERE "type" = "Receita" AND (date >= 2016-09-01 AND date <= 2016-09-30

Ai just adapt to the query of the Laravel.

2 - Change the database and place two fields in the table, year VARCHAR(4) and month VARCHAR(2) instead of just one of the kind date and maybe it’s easier to manipulate the data like this.