Getting specific date ranges from database in Laravel

Question

I need to get results from a DB divided by dates such as today, yesterday, this week, last week, etc.

I can easily do this with whereRaw and some SQL:

whereRaw('Date(created_at) = CURDATE()')->get();

I wonder if there is an easier, proper way to do this with Eloquent.

@AlexBlex This was just an example of how to get today, obviously when I want to get a range I will expand it. — prgrm
– prgrm, Commented Mar 8, 2017 at 14:04
If stackoverflow.com/questions/24824624/… , stackoverflow.com/questions/33361628/…, etc don't give you a hint, refer to the docs laravel.com/docs/5.4/queries#where-clauses? — Alex Blex
– Alex Blex, Commented Mar 8, 2017 at 14:13

Hanny · Accepted Answer · 2017-03-08 14:18:04Z

1

You could create a scope for a particular class like this:

public function scopeYourQuery($query, $user) {
    return $query->where('user_id', $user->id)->orderBy('created_at', 'desc')->first();
}

This just gets the first item of a descending ordered list ordered by created_at date per user.

If you wanted something that was between date ranges? You just pass in your date and extend it a bit with some PHP, maybe something like this would work:

public function scopeSomeDateQuery($query, $fetch_date, $user)
{
    //clone the users chosen month - so we can make the range until the following month
    $also_fetch_date = clone $fetch_date;
    $next_month = $also_fetch_date->addMonth();
    $next_month = $next_month->format('Y-m-d');
    $fetch_date = $fetch_date->format('Y-m-d');
    //return the query for the monthname
    return $query->orderBy('created_date')->where('created_date', '>=', $fetch_date)->where('created_date', '<', $next_month)->where('user_id', $user->id);
}

This would look in a monthly range (per user) to get an ordered list of items with a created_date in that range.

answered Mar 8, 2017 at 14:18

Hanny

2,1596 gold badges24 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prgrm Over a year ago

I was asking if there was a ready eloquent way to achieve this but since it seems there is not this is a good start. Thanks.

Hanny Over a year ago

Yeah, not that I am aware of - I think you have to craft the query yourself with your input. If you DO find a better way - let me know! I run up against similar queries often!

Collectives™ on Stack Overflow

Getting specific date ranges from database in Laravel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related