Eloquent get last record groupped by date

user_2357365 0
Viewed 48

I have a table prices with where I store more times in a day more values referred to a customer like this:

Table prices:

| id | customer_id | value_1 | value_2 | value_3 |      created_at      |
-------------------------------------------------------------------------
| 1  |     10      | 12345   |    122   |    10  |  2021-08-11 10:12:40 |
| 2  |     10      | 22222   |    222   |    22  |  2021-08-11 23:56:20 |
| 3  |     12      | 44444   |    444   |    44  |  2021-08-12 08:12:10 |
| 4  |     10      | 55555   |    555   |    55  |  2021-08-13 14:11:20 |
| 5  |     10      | 66666   |    666   |    66  |  2021-08-13 15:15:30 |
| 6  |     10      | 77777   |    777   |    77  |  2021-08-13 16:12:50 |

I have some filters on that table to retrieve only records with date greater than X and/or lower than Y, sort records by value_1 or value_2, etc... With that filters I have to take only 1 record for each day of a customer specified. I'm able to get the record with the highest value_1 for example, by using sql function max() and group by date.

// Init query
$query = Price::query();
// Take the greatest value of value1
$query = $query->selectRaw(
    'max(value_1) as highest_value_1, ' .
    'date(created_at) as date'
);

// If defined, add a greater or equals
if ($from) $query->where("created_at", ">=", $from);

// If defined add a lower or equals
if ($to) $query->where("created_at", "<=", $to);

// Get results for current customer only, grupping by date and ordering it
$query = $query->where('customer_id', $id)->groupBy('date')
    ->orderBy('date', 'DESC');
// Fetch records
$records = $query->get();

But now I would like to have only the last record for each day of a customer specified.

I need an eloquent/sql solution because the date range to search may be large and the table has a lot of records.

How can I archive that? Thanks

eloquent laravel sql php database
1 Answers

Not a complete solution (no customer filter nor laravel use), but I would use something like that in pure sql :

SELECT 
  CONCAT(EXTRACT(YEAR_MONTH FROM created_at),EXTRACT(DAY FROM created_at)) AS day, 
  MAX(created_at) 
FROM mytable 
GROUP BY day;

Of course, you may use other function to group by day (like string regexp or substr).

user_19978490
Related