Laravel Query get row based on 2 dependent column

Help me to query this.
I have a Laravel app where have a table that has 3 columns (quartal, year, value).

For example, I want to get the value from quartal 3 years 2019 to quartal 2 years 2020
how to make the query for this case?
for the year I can use between but for the quartal, It depends on the year.

here is my current query. but it does not work really well

DB::table($table)
->whereBetween('year',[$startYear, $endYear])
->whereBetween('quartal',[$startQuartal, $endQuartal])
->get();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

What you need to do is you need to create a compound column from quartal and year and then query that.

DB::table($table)
->whereRaw("concat(year,quartal) >= ?", "$startYear$startQuartal")
->whereRaw("concat(year,quartal) <= ?", "$endYear$endQuartal")
->get();

You can refer this for the SQL query https://www.db-fiddle.com/f/4GRH5RKqQi2Fc6wVvxK8C2/0

Method 2

you can do it with filter function also and it an easy way just like

$startYear = 2019
$endyear = 2020
$startQuartar = 3
$endQuartar = 2
DB::table($table)
->whereBetween('year',[$startYear, $endYear])
->get()->filter(function($query) use 
($startYear,$endYear,$startQuartar,$endQuartar){
    if($query->year == $startYear and $query->quartar < $startQuartar){
       return false;
    }
    if($query->year == $endYear and $query->quartar > $endQuartar){
      return false;
    }
      return true;
});

Method 3

How about adding the quartal to the year?

DB::table($table)
    ->whereBetween(
        DB::raw('year + (quartal - 1) / 4'),
        [
            $startYear + ($startQuartal - 1) / 4,
            $endYear + ($endQuartal - 1) / 4
        ]
    )
    ->get();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0