0

new to the community, would like to say thankyou to all the contributers!

Right now i'm needing a solution to get the Latest & Distinct value from a table.

The table I have is called 'transactions' which has 'id', 'value', 'remarks', 'memberId(FK)', 'created_at'.

ID | amount |  memberId | created_at
 1 |  101   |  00001    | 22-04-2018
 2 |  102   |  00002    | 22-04-2018
 3 |  103   |  00002    | 22-04-2018
 4 |  104   |  00001    | 24-04-2018 (latest)
 5 |  105   |  00002    | 25-04-2018 (latest)
 6 |  106   |  00003    | 25-04-2018 (latest)

my expected result is

ID | amount |  memberId | created_at
 4 |  104   |  00001    | 24-04-2018
 5 |  105   |  00002    | 25-04-2018
 6 |  106   |  00003    | 25-04-2018

My code

      $transactions = Transaction::select('id', 'memberId', 'amount', DB::raw('max(created_at) as latest_depo'), 'created_at', 'amount')
   ->groupBy('memberId')
   ->orderBy('latest_depo' , 'desc')
   ->paginate(50);

but I end up not getting the Latest Row of each member. My guess is something to do with the grouping of the values. Thanks in advance!

jarlh
  • 42,561
  • 8
  • 45
  • 63
jonathan sebastian
  • 1

2 Answers2

0

How about:

$transactions = Transaction::latest()
    ->groupBy('memberId')
    ->distinct('memberId')
    ->select('id','amount','memberId','created_at')
    ->paginate(50);

Edit: I added groupBy() to the above query. If groupBy() gives you an error, verify your database configuration as described here.

Note: If you are using Laravel Eloquent relationships, your memberId column should probably named member_id.

LobsterBaz
  • 1,752
  • 11
  • 20
  • Thanks, but this still gives me multiple rows of data, i tried adding groupBy('memberId'), which groups them according to their memberId but not the latest 'created_at'. – jonathan sebastian Apr 07 '20 at 02:53
  • @jonathansebastian I've edited my answer and added `groupBy('member_id')` to the query. – LobsterBaz Apr 07 '20 at 18:12
0

A little late to answer this but came across it as I had a similar issue so thought I'd post in case it helps someone else.

I think you can use ->orderBy(), get() and then use ->unique() on the result. You would need to remove the groupBy() and the max() like so:

$transactions = Transaction::select('id', 'member_id', 'amount', 'created_at', 'amount')
  ->orderBy('created_at' , 'desc')
  ->get()
  ->unique('member_id');

The only thing I'm not sure about is using it with paginate(). Probably too late for OP but hopefully it helps someone else.

rowBawTick
  • 11
  • 1