Eloquent, sort by date difference

I have a table with a created_at field. I want to load the contents of this table and add an extra key to the result where i calculate the difference between the created_at field and a certain date. I need to order by this difference and use the difference in the view.

Is there a way to query eloquent so i can have this field sorted and all in my result. Or do i have to loop through the results and calculate the diff per result? (i can then of course just order by the created_at field.)

What would be the best way to do this?

Example: table:

| id| key|created_at|
| --- | --- | --- |
| 1 | foo |2016-01-01 22:00:00|
| 2 | bar |2016-01-04 22:00:00|
| 3 | fuz |2016-01-02 22:00:00|

when i query using the date 2016-01-01 22:00:00 i would get an result looking like this:

$result = [
    [
        'id' => 1,
        'key' => 'foo',
        'created_at' => '2016-01-01 22:00:00',
        'diff' => 0,
    ],
    [
        'id' => 3,
        'key' => 'fuz',
        'created_at' => '2016-01-02 22:00:00',
        'diff' => 1, // day
    ],
    [
        'id' => 2,
        'key' => 'bar',
        'created_at' => '2016-01-04 22:00:00',
        'diff' => 3, //days
    ]
]

igorblumberg

10 years ago

Best Answer

Level 7

@RobertBaelde On your model implement this:

class YourClass extends Model
{
    protected $dates = ["created_at","updated_at","certain_date"]; //add this and the certain_date attribute will be a carbon instance
    protected $appends = ["certain_date"];//add this to append the certain_date accessor, so anytime you query this model this attribute will be fetched (or calculated)
    
    public function getCertainDateAttribute()
    {
        //implent this method to return the difference you need
        //for example, return the difference between created timestamp and now
        $now = Carbon::now();
        return $this->created_at->diffInDays($now);
    }
}

Then you can use the "certain_date" attribute as you would use the created_at

I didn't test the code, but the overall idea should work.

Good luck!

Please or to participate in this conversation.