get contracts entries based on documents expiration

I have this query in laravel 8:

DB::table('contracts')
  ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
  ->whereNotNull('expiration_date')
  ->whereMonth('expiration_date', '01')
  ->whereYear('expiration_date', '2022')
  ->select('documents.contract_id', 'documents.is_contract', 'documents.expiration_date')
  ->get();

Contracts - Documents (one to many)

expiration_date is in Documents table and is nullable.

If the contract has 2 documents with expiration_date filled then it will return both entry (for the same contract).

I want to get the last entry for expiration_date from documents (only one entry for the contract)

ex: If a contract has 2 documents with expiration_date set ... I want to take only the last expiration_date value for that contract.

How can I do that ?

CodePudding user response：

Try

Contracts::with('documents', function($query) {
    $query->latest('expired_at')->first;
})->get();

CodePudding user response：

Please try this : orderByDesc or latest will get the latest expiration_date.

DB::table('contracts')
      ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
      ->select('documents.contract_id', 'documents.is_contract', 'documents.expiration_date')
      ->whereNotNull('expiration_date')
      ->whereMonth('expiration_date', '01')
      ->whereYear('expiration_date', '2022')  
      ->orderByDesc('expiration_date')
      ->first();

OR you can try this:

DB::table('contracts')
      ->leftJoin('documents', 'contracts.id', '=', 'documents.contract_id')
      ->select('documents.contract_id', 'documents.is_contract', 'documents.expiration_date')
      ->whereNotNull('expiration_date')
      ->whereMonth('expiration_date', '01')
      ->whereYear('expiration_date', '2022')        
      ->latest('expiration_date')
      ->first();