Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello, This is a question both in eloquent/SQL since both options could work.
I have a table called mailinglists, where my laravel app fetches data about all different mailinglists we have every hour from a third party and store it in our mailinglist model. So we get the data every hour per day which means that we have 24 records in the database per day / mailinglist.
What we are trying to achieve now but have runned into issues is: Get the amount of members each mailinglist have, per day, from the latest record.
This is our original:
| id | created_at | list_id | list_name | members | |---- |------------------ |--------- |------------- |--------- | | 1 | 2022-04-22 09:34 | 983343 | Nice list | 86690 | | 2 | 2022-04-22 21:00 | 983343 | Nice list | 86669 | | 3 | 2022-04-22 23:00 | 983343 | Nice list | 86710 | | 4 | 2022-04-23 00:00 | 983343 | Nice list | 86730 | | 5 | 2022-04-23 23:00 | 983343 | Nice list | 86780 | | 6 | 2022-04-22 09:34 | 983344 | Pretty list | 73 | | 7 | 2022-04-22 22:00 | 983344 | Pretty list | 71 | | 8 | 2022-04-23 01:00 | 983344 | Pretty list | 65 | | 9 | 2022-04-23 09:00 | 983344 | Pretty list | 62 |
| id | created_at | list_id | list_name | members | |---- |------------------ |--------- |------------- |--------- | | 3 | 2022-04-22 23:00 | 983343 | Nice list | 86710 | | 5 | 2022-04-23 23:00 | 983343 | Nice list | 86780 | | 7 | 2022-04-22 22:00 | 983344 | Pretty list | 71 | | 9 | 2022-04-23 09:00 | 983344 | Pretty list | 62 |
Since we are fairly new to Laravel we still usually write the stuff we want in SQL, and then try to see if we can convert it into query builder / eloquent when possible.
However we ran into an issue even at the SQL part here.
If we would write:
SELECT date(created_at), list_id, name, members FROM mailinglists
GROUP BY date(created_at), list_id;
Then the result would be that we would get the FIRST record for each day, while we want the last record.
Anyone able to assist / pinpoint us in correct direction?
You could ry this
SELECT r2.date,r1.list_id,r1.name, r1.members FROM mailinglists r1
INNER JOIN ( SELECT MAX(created_at) AS last,list_id FROM mailinglists GROUP BY date(created_at),list_id ) r2
ON r1.created_at=r2.last AND r2.list_id=r1.list_id
in Laravel use DB::select(...
Thank you so much, that you took the time to help out even though I had some issue with my markdown table.
It seems to work perfect at a first glance!
Please or to participate in this conversation.