Search code examples
phpmysqlsqllaravellaravel-5.3

How to get one records that sorted by date

I have a table like this:

id    product_id   price      date
1       1001        100     2017-05-29
2       1005        101     2017-03-30
3       1001        102     2017-03-30
4       1003        106     2017-06-07
5       1005        106     2017-04-30

I want to group by product_id and get only one latest product for each product_id. I also have a min and max price variable which should be applied to the price column.

the result of my query should be:

1       1001        100     2017-05-29
4       1003        106     2017-06-07
5       1005        106     2017-04-30

Is it possible by SQL or I should use application side?

Solution

  • If you want to order results by date, just use orderBy():

    Model::orderBy('date_column', 'desc')->whereBetween('price', [$min, $max])->groupBy('product_id')->take(3)->get();

    Or it's shortcuts latest() and oldest():

    Model::latest('date_column')->...