Show only the latest record

I have two tables a payments table and a tenants table. Each payments table can have many payments for a single tenant (one to many relationship)

Payments 
id    tenant_id   amount 
1     1                  5000    
2     1                  6000
3     2                  7000    
4     2                  8000

tenants 
id    name         email
1     peter         [email protected]
2     grace         [email protected]

When i run the following in my controller

public function getPaymentsList(Request $request)
    {
       $payments = DB::table("payments")
       ->join('tenants','tenants.id','=','tenant_id')
       ->select('payments.id','payments.tenant_id','tenants.name','payments.rent_to')
       ->groupBy('tenant_id')->get()
;      
      
 return  response()->json($payments);
    }
   
}

I get json content of the oldest record in the payments table not the latest record.

[
{
"id": 1,
"tenant_id": 1,
"name": "peter",
"amount": "5000"
},
{
"id": 3,
"tenant_id": 2,
"name": "grace",
"amount": "7000"
},
]

The output i want is

[
{
"id": 2,
"tenant_id": 1,
"name": "peter",
"amount": "6000"
},
{
"id": 4,
"tenant_id": 2,
"name": "grace",
"amount": "8000"
},
]

Can someone improve for me the above query so that it can display with the desired output. Kindly help

LeNiglo

5 years ago

Level 1

Hi Kapchorwa,

My guess is that you need to add an orderBy to your query.

$payments = DB::table("payments")
       ->join('tenants','tenants.id','=','tenant_id')
       ->select('payments.id','payments.tenant_id','tenants.name','payments.rent_to')
       ->orderBy('payments.created_at', 'DESC')
       ->groupBy('tenant_id')->get()

Edit: if you don't have the created_at field like you described, feel free to use payments.id instead ...

Does it help you ?

kapchorwa

5 years ago

Level 1

Hi Thank you for the input but when i try orderBy it just reorders the same output in descending order but it does not select the records with the highest id per group.

Snapey

5 years ago

Best Answer

Level 122

Do you use Eloquent:

create relationship latestPayment in Tenant model

public function latestPayment
{
	return $this->hasOne(Payment::class)->latest();
}

then load them

$tenants  = Tenant::with('latestPayment')->get();

Now you have all tenants with a single latestPayment relation

1 like

kapchorwa

5 years ago

Level 1

works. thank you.

Snapey

5 years ago

Level 122

please mark best answer

Please or to participate in this conversation.