Вопрос:

Get latest record using multiple column values

mysql laravel eloquent

52 просмотра

2 ответа

422 Репутация автора

I have a table that stores user purchase history:

Table Purchases : id, user, brand, model, color, date

I'd like to get a list of latest purchases made by a user, grouped by brand and model.

Here is an example dataset:

1, Bob,  Apple,   iPhone 6, Gold,   2017-01-03
2, Bob,  Apple,   iPhone 6, Silver, 2017-01-09
3, Bob,  Apple,   iPhone 6, Gold,   2017-01-18
4, Bob,  Samsung, Galaxy,   Black,  2017-01-22
5, Bob,  Samsung, Galaxy,   Black,  2017-01-28
6, Jane, Apple,   iPhone 6, Silver, 2017-01-18
7, Jane, Apple,   iPhone 6, Silver, 2017-01-22
8, Jane, Samsung, Galaxy,   White,  2017-01-28

I'd like to retrieve display a list like so:

2, Bob,  Apple,   iPhone 6, Silver, 2017-01-09
3, Bob,  Apple,   iPhone 6, Gold,   2017-01-18
7, Jane, Apple,   iPhone 6, Silver, 2017-01-22
8, Jane, Samsung, Galaxy,   White,  2017-01-28

So that I can see the latest date of purchase for each variation of a phone.

I tried GROUP BY brand, model, color, but it doesn't return the latest row as ordering before group is not supported(?).

Please let me know if I can clear anything up. Thanks in advance.

Автор: Sage Arslan Источник Размещён: 08.11.2017 11:18

Ответы (2)


-1 плюса

177 Репутация автора

You can use the laravel query builder

$purchases = DB::table('Purchases')
            ->groupBy('brand', 'model', 'color')
            ->orderBy('date', 'desc')
            ->get();
Автор: Nisarg Shah Размещён: 09.11.2017 12:20

0 плюса

163 Репутация автора

Try this

$date = array();
foreach ($purchase as $key => $row)
{
    $date[$key] = $row['date'];
}
array_multisort($date, SORT_ASC, $purchase);

Автор: Olasunkanmi Размещён: 09.11.2017 12:38
32x32