Problems when using join and pagination together in Laravel

I have some tables in my database and I’m trying to transact between them. My aim is to show the project name and the employees in that project in the form of cards on my projects page as in the image below. (Other than the method I mentioned, if there are better ways to do this, I ask you to share.)

When trying to do this, the first thing that came to my mind was to use leftJoin. Together with the projects, I thought of pulling the users in that project together and showing them by using foreach nested. If I just want to shoot projects, there will be no problem and the first 4 projects come and everything works fine. But since I needed to attract the users in that project along with my projects, when I used leftJoin, I got an output as you can see below. I had to use pagination as I needed to show 4 projects per page, and when I used pagination, the lines that came in were inconsistent. While waiting for 4 projects and their employees to arrive, the first 4 lines came. (3 employees of 1 project and the first employee of the other project have arrived.)

My code:

$query = Project::query();
$query->select('prj_name', 'us_name');
$query->where('up_type_id', 3);
$query->leftJoin('user_positions', 'projects.prj_id', '=', 'user_positions.up_source_id');
$query->leftJoin('users', 'user_positions.up_user_id', '=', 'users.us_id');
$data['projectList'] = $query->paginate(4)->toArray();
return view('pages.projects.index', $data);

Output:

[data] => Array
        (
            [0] => Array
                (
                    [prj_name] => Project Name - 2
                    [us_name] => Tracey Dyer
                )

            [1] => Array
                (
                    [prj_name] => Project Name - 2
                    [us_name] => Joseph Dickens
                )

            [2] => Array
                (
                    [prj_name] => Project Name - 2
                    [us_name] => Steven Ogden
                )

            [3] => Array
                (
                    [prj_name] => Project Name - 3
                    [us_name] => Warren Nash
                )

        )

My tables:

Additional info : It is not directly related to my question, but I would like to state it for informational purposes. My table named “user_position_types” represents fields within the company. The “up_type_id” column in the “user_positions” table specifies the partition type (such as Department, Project). If this column is 1, it means department and “source_id” indicates which department it is. If it is 3, it means project and again “source_id” indicates which project it is.

I would like to thank everyone who answered in advance.

Answer

You may consider using Eloquent relationship instead of left join. In fact left join wouldn’t work that well in your case since you will have to group all users in the same project from the rows afterwards. Example of Eloquent relationship using whereHas and with as follows.

$data['projectList'] = Project::select('prj_name', 'us_name')
    ->whereHas('users, function($query) {
        $query->where('users_positions.up_type_id', 3);
    })
    ->with('users')
    ->paginate(4);

The whereHas part filters only projects with users relationship and has pivot field up_type_id 3.
And with part will load users corresponding to the project.

You will also need to add a users relationship in Project model.

public function users()
{
    return $this->belongsToMany(User::class, 'user_positions', 'prj_id', 'us_id')
        ->withPivot('up_type_id');
}

With this you can show your projects and users like so.

foreach($data['projectList'] as $project) {
    $project->prj_name;
    foreach($project->users as $user) {
        $user->us_name;
    }
}

In case you would like to have either projects with no users and projects with users from up_type_id = 3, you may use where closure and filter from both conditions (use orWhereHas).

$data['projectList'] = Project::select('prj_name', 'us_name')
    ->where(function ($query) {
        $query->doesntHave('users')
            ->orWhereHas('users, function($query) {
                $query->where('user_positions.up_type_id', 3);
            });
    })
    ->with('users')
    ->paginate(4);