CODEWITHSUNDEEP
phplaraveleloquentlumenphp-carbon
JonnySerra
JonnySerra

Reputation: 1084

How to generate a list of unique years between several dates in Lumen/Laravel

i'm creating an API in Lumen and i need to create a method that will get dates from two colums on the same table and return any years that occur between those dates, returning them all as a single array.

So for instance, imagine a table column named start_date and another named end_date

start_date | end_date          getAllYears() should return =>
                               [1983, 1984, 1985, 1986, 1987,
                               1999, 2000, 2001, 2002, 2003,
                               ..., 2016]
1999-05-09 | 2002-04-03
1983-03-12 | 1987-09-23
2001-02-12 | 2016-11-27

Currently i have a method that manages to do this on other types of more specific queries, the major problem with this attempt, is that due to the sheer mass of SQL records that i'm retrieving, a method like this causes my request to time out every single time.

MY INEFFICIENT METHOD that makes little use of Lumen/Laravel

public function getAllYears(){
    $dates = $this->model->select('data_ini', 'data_fim')->get();

    $results = [];

    foreach ($dates as $obj){
        $carbonBegin = Carbon::createFromDate($obj->data_ini->year);
        $carbonEnd   = Carbon::createFromDate($obj->data_fim->year);

        if($carbonEnd->year === 9999){
            $carbonEnd->year = date('Y');
        }

        $carbonEnd->year++;
        // Simple method that runs a DatePeriod method
        $dateRange = $this->helper->createDateRange($carbonBegin, $carbonEnd);

        $results = array_merge($results, $dateRange);
    }
    sort($results);
    $cleanYears = array_unique($results);

    if ($cleanYears == null)
        return response()->json(['error' => true, 'errorCode' => '1008', 'message' => "No years found!"]);
    else
        return response()->json(['error' => false, 'years' => $cleanYears]);
}

So, the question is, how can i do this in a less expensive way so that my server doesn't time out on every request? Thank in advance for your help :)

NOTE: DB:raw is a no-go as my TL has forbidden me from using it anywhere on the API

Upvotes: 0

Views: 250

Answers (1)

Derek Pollard
Derek Pollard

Reputation: 7165

Looks like you need the whereBetween:

$between = DB::table('theTable')->whereBetween('data_ini', ["str_to_date('2011-05-06','%Y-%m-%d')", "str_to_date('2011-05-06','%Y-%m-%d')"])->get();

With models:

$between = $this->model->whereBetween('data_ini', ["str_to_date('2011-05-06','%Y-%m-%d')", "str_to_date('2011-05-06','%Y-%m-%d')"])->get();

In the above, I am utilizing MySQL's built-in str_to_date

Hope this helps!

Upvotes: 0

Related Questions