Retrieving Biggest Date Difference Between Rows

I'm trying to figure out how to structure my query so that in a table where I have 2 date fields. I want to get the records that have the greatest difference between both dates. If date #2 does not have a date then use the current date with Carbon::today or NOW() if need be.

So fields are the won_on date and lost_on date.

public function longestTitleReign(Title $title)
{
        return Champion::with('wrestler')
            ->select('champions.lost_on', 'champions.won_on', 'wrestler_id')
            ->where('title_id', $title->id)
            ->orderByTimeAsChampion('desc')
            ->get();
}

public function scopeOrderByTimeAsChampion(Builder $query, $direction = 'asc')
{
        return $query->orderByRaw('DATEDIFF(lost_on, NOW()) ' . $direction);
}

Cronix

7 years ago

Level 67

If date #2 does not have a date then use the current date with Carbon::today or NOW() if need be.

You'll need to use a CASE statement to do that

https://dev.mysql.com/doc/refman/5.7/en/case.html

1 like

Tray2

7 years ago

Level 73

You can also use the IFNULL function.

SELECT field1, IFNULL(field2, SYSDATE) FROM table1;

If field two is null it will substitute it with sysdate.

1 like

jrdavidson

7 years ago

Best Answer

Level 54

Those are both excellent suggestions. I debated about this and for readability I ended up going a different route.

public function longestTitleReign(Title $title)
{
        $maxDateDiff = Champion::selectRaw('MAX(DATEDIFF(IFNULL(lost_on, NOW()), won_on)) AS diff')->value('diff');

        return Champion::with('wrestler')
            ->select('champions.lost_on', 'champions.won_on', 'wrestler_id')
            ->where('title_id', $title->id)
            ->whereRaw("DATEDIFF(IFNULL(lost_on, NOW()), won_on) = {$maxDateDiff}")
            ->get();
}

Please or to participate in this conversation.