[FIXED] How to use a WHERE on eloquent for comparing a date created by year and month column in a table for Laravel?

Issue

public function scopeStartsFrom(Builder $query, $month, $year): Builder
{
    return $query->addSelect(DB::raw("str_to_date(concat(year,'-',month,'-',1), '%Y-%m-%d') as carbon"))->where('carbon', '>=', Carbon::createFromDate($year, $month, 1));
}

I have a table with separate year and month columns. How to make it so that I can use the year/month columns into a date that I can use for my scopes. So, if I enter year 2000 and month 10, it will output all rows that starts from that year and month? Thanks! I am using Laravel 8, Spatie Query Builder, and MySQL.

year | month 2009 | 12 2013 | 1 2011 | 5 2010 | 10

So if i enter year 2010 and month 11, it will output the second and third row only.

Solution

I think that calculation is too expensive. When you give year and month there can be only two comparations.

1. If year is greater than given, then date is definitely greater than given date
2. If year is same but month is greater or equal.

So I think the correct query should be:

public function scopeStartsFrom(Builder $query, $month, $year): Builder
{
    return $query->addSelect(
        DB::raw("str_to_date(concat(year,'-',month,'-',1), '%Y-%m-%d') as carbon")
      )->where(function($q) use ($year, $month) {
        $q->where('year', '>', $year)
          ->orWhere([
            ['year', '>=', $year],
            ['month', '>=', $month]
          ]);
      });

    // Generated query: 
    SELECT * FROM table WHERE (year > 2022 OR (year >= 2022 AND month >= 4))
}

Answered By - Malkhazi Dartsmelidze

Answer Checked By - Senaida (PHPFixing Volunteer)