Laravel 5.5一对多关系：统计最后交互type为'in'的乘客数量

Hey there! Let’s work through this problem together. You already have a solid SQL query that gets the job done, so we can either translate that directly into Laravel’s query builder, or use Eloquent relationships for a more expressive, Laravel-native approach. Here are both options:

Option 1: Mirror Your Raw SQL with Query Builder #

This approach sticks close to the SQL logic you already know works, using Laravel’s query builder methods to replicate it:

$count = DB::table('passengers as p')
    ->join('histories as h1', 'p.id', '=', 'h1.passenger_id')
    ->leftJoin('histories as h2', function ($join) {
        $join->on('p.id', '=', 'h2.passenger_id')
             ->where(function ($whereClause) {
                 // Check if h2 has a newer record (either later created_at, or same time but higher id)
                 $whereClause->where('h1.created_at', '<', 'h2.created_at')
                             ->orWhere(function ($subClause) {
                                 $subClause->where('h1.created_at', '=', 'h2.created_at')
                                           ->where('h1.id', '<', 'h2.id');
                             });
             });
    })
    ->whereNull('h2.id') // Marks h1 as the latest record for the passenger
    ->where('h1.type', 'in')
    ->count('p.id'); // Count unique matching passengers

Option 2: Use Eloquent Relationships (Cleaner ORM Style) #

First, make sure your Passenger model has a properly defined relationship to the History model:

// app/Passenger.php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Passenger extends Model
{
    public function histories()
    {
        // Order records to prioritize the latest entry first
        return $this->hasMany(History::class)->orderBy('created_at', 'desc')->orderBy('id', 'desc');
    }
}

Then use a subquery to filter passengers whose latest history entry has a type of 'in':

// Define a subquery to get the latest history type for each passenger
$latestHistoryType = History::select('type')
    ->whereColumn('passenger_id', 'passengers.id')
    ->orderBy('created_at', 'desc')
    ->orderBy('id', 'desc')
    ->limit(1);

// Count passengers where their latest entry is 'in'
$count = Passenger::whereExists(function ($query) use ($latestHistoryType) {
    $query->select(DB::raw(1))
          ->fromSub($latestHistoryType, 'latest_history')
          ->where('latest_history.type', '=', 'in');
})->count();

For a more readable (though slightly less performant for large datasets) alternative, use whereHas:

$matchingPassengers = Passenger::whereHas('histories', function ($query) {
    $query->select('type')
          ->whereColumn('passenger_id', 'passengers.id')
          ->orderBy('created_at', 'desc')
          ->orderBy('id', 'desc')
          ->limit(1)
          ->having('type', '=', 'in');
})->get();

$count = $matchingPassengers->count();

Quick Notes on Choosing an Option #

• Query Builder: Best for raw performance, especially with large datasets, since it directly uses your optimized SQL logic.
• Eloquent: Better for readability and keeping your code aligned with Laravel’s ORM patterns, great if your project leans heavily on Eloquent models.

内容的提问来源于stack exchange，提问作者Leandro Parice