I have three tables

1. users
2. passports
3. sections

I want to get the user and his latest passport. The user is in one section and can have several (history) passports. Active passport with maximum passportDate

USERS

Table 
            ...
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->foreignId('section_id')->nullable()->constrained();
            ...

Section

Table
            ...
            $table->string('title');
            $table->string('description');
            ...

Passport

...
$table->date('passportDate')->nullable();
$table->string('firstName')->nullable();
$table->string('middleName')->nullable();
$table->string('lastName')->nullable();
$table->foreignId('user_id')->constrained()->onDelete('cascade');
...

I want to get a set of fields for each user:

 "email" "section_title"  "firstName"  "middleName" "lastName"

My request:

$users = User::select('users.*', 'sections.title', 'passports.firstName', 'passports.middleName', 'passports.lastName')
                            ->leftJoin('sections', 'sections.id', 'users.section_id')
                            ->leftJoin('passports', 'user_id', 'users.id')
                            ->orderBy('firstName')
                            ->get();

The problem is, if a user has multiple passports, the user's entry is duplicated. I need to get for each user data about his last (max passportDate) passport

CodePudding user response：

Can try this:

$users = User::select('users.*', 'sections.title')
               ->leftJoin('sections', 'sections.id', 'users.section_id')
               ->orderBy('firstName')
               ->get();
$users->each(function ($item, $key){
$item->passports = Passport::select('firstName', 'middleName', 'lastName')
                  ->where('user_id', $item->id)
                  ->get();
}

So on this Passport data will be separate array and you can add any conditions for that

CodePudding user response：

You could use a relationship to get the latest passport (or get your sections or all passports)

On your user model define the relationship:

public function latestPassport() {
    return $this->hasOne(Passport::class)->latest('passportDate');
}

And then add the relationship to the query:

$users = User::select()
    ->with(['sections', 'latestPassport'])
    ->orderBy('firstName')
    ->get();