Level 104
So you want the first record where the effective date is less than or equal to the given date?
Model::query()
->whereDate('effective_date', '<=', $currentDate)
->orderBy('effective_date', 'desc')
->first()
Sep 21, 2022
14
Level 1
Eloquent & Dates
Hello,
I have a particular query i'm trying to solve regarding multiple dates.
I have a pricing table, and in that table each price has an effective date e.g.
ID Price Currency Effective Date
1 | 9.99 | GBP | 2022-09-01
2 | 10.99 | GBP | 2022-09-10
3 | 11.99 | GBP | 2022-09-20
What i want is to show the price that is currently in scope,
So if the date today was 2nd September, then the price that comes first is 9.99.
If the date was then 10th September then the result will return 2 result and the price 10.99 becomes active and the price 9.99 drops off because that date is succeeded by row 2
Then if the date was then 20th September then the result will return 1 result and the price is 11.99.
Anyone know how i can achieve this?
So far i'm ordering the query by effective date so that it's at least in the correct order, but if the date was 10th September then the first row will still show.
thanks!!
Level 1
@tykus Hi Tykus,
This would give me the first date, but there are instances where the first row returned the date isn't effective yet, but we still want to show it. So i don't want to filter out dates, just only filter if the first row date has passed the second row date.
Level 104
@addtobag if the query is sorted in descending order by the effective_date, then the first record satisfying the where constraint will be the correct record
Level 1
ID Price Currency Effective Date
3 | 11.99 | GBP | 2022-09-25
I see what you're saying, it doesn't work when there is 1 row though, and todays date is before the effective date. The above would return 0 results. In this case i would want to show the future date.
Level 104
@addtobag you would need to make a UNION query to ensure that exactly one row was returned:
Model::query()
->whereDate('effective_date', '<=', $currentDate)
->orderBy('effective_date', 'desc')
->union(
Model::whereDate('effective_date', '>', $currentDate)
->orderBy('effective_date', 'asc')
)
->first()
Level 122
Level 1
Something like this?
public function currentPrice()
{
return $this->hasOne('App\Models\Price', 'product_group_id', 'product_group_id')
->whereDate('effective_date', '<=', Carbon::now())
->orderBy('effective_date', 'desc')
->union(
Price::whereDate('effective_date', '>', Carbon::now())
->orderBy('effective_date', 'asc')
);
}
Level 55
@addtobag I think it can be achived via oneOfMany relationship https://laravel.com/docs/9.x/eloquent-relationships#has-one-of-many
public function currentPrice()
{
return $this->hasOne(Price::class)->latestOfMany('effective_date')->whereDate('effective_date', '<=', today());
}
(be sure you set correct models/columns names)
This way you'll be able to load products (or whatever you have) with the price
$products = Product::with('currentPrice')->paginate();
foreach ($products as $product) {
dump($product->currentPrice->price);
}
Level 1
Thank you for your reply.
I didn't know of latestOfMany() thats really helpful.
I would still be filtering out any date that is equal to or less than today. I want to only filter if the first row date has passed the second row date.
Thanks
Ross
Level 55
@addtobag your description is not clear then. Try to exaplin more detailed
Level 1
I explained it here
"So if the date today was 2nd September, then the price that comes first is 9.99."
9.99 being the first row with an effective date (2022-09-01) which is less than 2nd September. But with your recommendation it filters that first row out.
ID Price Currency Effective Date
1 | 9.99 | GBP | 2022-09-01
2 | 10.99 | GBP | 2022-09-10
3 | 11.99 | GBP | 2022-09-20
Level 55
whereDate('effective_date', '<=', today())
2022-09-01 is < today() (2nd September), so, it will be returned
Why it's filtered out?
Level 6
Hi , The way you would do this query is with either whereBetween() or where() .
https://laravel.com/docs/9.x/collections#method-wherebetween
Level 1
Hi @Elliot_putt
Thank you for your reply.
It's not as simple as a whereBetween or a where clause i've found.
Thankls
Please or to participate in this conversation.