last post leftjoin

Hello. I have three tables

users
passports
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

vincent15000

4 years ago

Level 63

I would try this.

$users = User::
	with('section')
	->with(['passport' => function ($query) {
		$query->orderBy('passportDate')->last();
	}])
	->get();

Tell me if it helps ;).

Waik

4 years ago

Level 1

@vincent15000 error

Call to undefined relationship [passport] on model [App\Models\User].

If i cange in query to Passports. ->with(['passport' => function ($query) { error

Call to undefined method Illuminate\Database\Eloquent\Relations\HasMany::last()

vincent15000

4 years ago

Level 63

@Waik You have to declare the relationship in your model.

// User model

public function passport()
{
	return $this->hasMany('App\Models\User');
}

And sorry it is not the last()method but the latest() method.

$users = User::
	with('section')
	->with(['passport' => function ($query) {
		$query->orderBy('passportDate')->latest();
	}])
	->get();

1 like

Waik

4 years ago

Level 1

@vincent15000 thanks Corrected to latest(), the request is being processed. But how can I get the passport fields from it?

Waik

4 years ago

Best Answer

Level 1

Solved

        $users = User::select('users.*', 'sections.title', 'passports.firstName', 'passports.middleName', 'passports.lastName')
                  ->leftJoin('sections', 'sections.id', 'users.section_id')
                  ->leftJoin('passports', function($query) {
                      $query->on('passports.user_id','=','users.id')
                          ->whereRaw('passports.passportDate IN (select MAX(a2.passportDate) from passports as a2 join users as u2 on u2.id = a2.user_id group by u2.id)');
                  })
                  ->get();

Please or to participate in this conversation.