How to get latest record on a relationship in Laravel

Question

Users have plans stored in the invoices table. These plans are monthly based.

What I need to do

I want to add a new row for user if his plan expire date has reached and they didn't renew their plans (I don't want to update old one)

The issue is

Each user has unlimited rows in invoices table as they renew each month. Now when I try to retrieve their latest row and check the expiring date it gets other rows of those users as well.

Example

1. My user has 3 rows in invoices
2. two of them already expired and renewed, the current one is id=3
3. when I try to expire this id=3 and create id=4 for this user
4. it gets all 3 rows and send 3 emails to the user.

Code

public function handle()
    {
        $invoices = Invoice::where('plan_expire', '<=', Carbon::now())->get();
        $current = Carbon::now();
        $expiredatetime = $current->addDays(30);
        $useType = Type::where('name', 'Free')->first();

        foreach($invoices as $invoice)
        {
            Invoice::create([
                'user_id' => $invoice->user->id,
                'type_id' => $useType->id,
                'amount' => $useType->price,
                'status' => 'Approved',
                'plan_expire' => $expiredatetime->toDateTimeString(),
            ]);
            Mail::to($invoice->user->email)->send(new UserPlansReset($invoice));
        }
    }

User model

public function invoices()
{
  return $this->hasMany(Invoice::class);
}

Invoice model

protected $fillable = [
        'user_id', 'type_id', 'amount', 'status', 'plan_expire',
    ];

    protected $casts = [
        'plan_expire' => 'datetime',
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

Question

Do you have any idea how I can only get users latest row in invoices table?

Update

based on answers below I changed my code to:

$current = Carbon::now();
        $expiredatetime = $current->addDays(30);
        $useType = Type::where('name', 'Free')->first();

        $users = User::all();
        foreach($users as $user){
          $latestInvoice = $user->invoices()->latest()->first();

          if(!empty($latestInvoice) && $latestInvoice->plan_expire <= Carbon::now()){
              Invoice::create([
                  'user_id' => $user->id,
                  'type_id' => $useType->id,
                  'amount' => $useType->price,
                  'status' => 'Approved',
                  'plan_expire' => $expiredatetime->toDateTimeString(),
              ]);
              Mail::to($user->email)->send(new UserPlansReset($user));
          }
        }

Now this function will return

Expected response code 220 but got an empty response

and wont send emails.

Answer 1

Find expired invoices, group by user id and order by plan_expire and select first record in each group.

MySQL server version < 8 don't have window functions that may make it easier to do row numbering in matched rows.

A workaround is to set client variables that can be used to number invoices by the same user starting from 1 and selecting only the first ones.

$now = Carbon::now();
$nowDS = $now->toDateTimeString();

$expired_invoices = "
SET @rownum := 0;
SET @userid := NULL;

SELECT *, uid as user_id, plan_expire 
FROM (
    SELECT 
        *,
        @rownum := CASE
            WHEN @userid = uid 
            THEN @rownum + 1
            ELSE 1
            END AS rn,
        @userid := user_id AS uid,
        num
    FROM invoices AS i
    WHERE plan_expire <= $nowDS
    ORDER BY user_id, plan_expire DESC
) AS num_invoices WHERE rn = 1;
"

$invoices = DB::select($expired_invoices);

Now, $invoices can be iterated over and mail sent to the owner of it.

$expiredatetime = $now->addDays(30);
$useType = Type::where('name', 'Free')->first();
$users = User::all();


foreach ($invoices as $invoice)
{
    Invoice::create([
        'user_id' => $invoice->user_id,
        'type_id' => $useType->id,
        'amount' => $useType->price,
        'status' => 'Approved',
        'plan_expire' => $expiredatetime,
    ]);

    $user = $users->find(['id' => $invoice->user_id]);
    Mail::to($user->email)->send(new UserPlansReset($user));
}

Answer 2

Change in Invoice model, add plan_expire in $dates variable instead of $casts :

protected $dates = ["plan_expire"];

You can try like this :

$users = User::all();
foreach($users as $user){

  $latestInvoice = $user->invoices()->latest()->first();

  if($latestInvoice->plan_expire->isPast()){

       //create invoice and mailed it
  }
  //other steup

}

For Email send return empty response issue , You can check this question click here