CODEWITHSUNDEEP
phplaravel
Nick
Nick

Reputation: 189

Count how many users are there per each category

I'm trying to get user data, them get department value to array so I can group it and count how many users are part of the same department.

User table:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();         
});

Department table:

Schema::create('departments', function (Blueprint $table) {
    $table->increments('id');
    $table->timestamps();
    $table->string('name');
});

Assignment table:

Schema::create('assignments', function (Blueprint $table) {
     $table->increments('id');
     $table->timestamps();
     $table->string('name');
     $table->unsignedInteger('user_id')->nullable();
     $table->foreign('user_id')->references('id')->on('users');   
     $table->unsignedInteger('department_id')->nullable();
     $table->foreign('department_id')->references('id')->on('departments');             
});

public function setDepartment($users)
{
        $departmentArray = [];
        $users = DB::table('users')
        ->leftJoin('assignments','assignments.user_id','=','users.id')
        ->leftJoin('departments','assignments.department_id','=','department.id')
            ->select('assignments.id as assignments',
                     'assignments.name as assignment_name',
                     'departments.id as dept_id',
                     'departments.name as dept_name',
                     'users.id as user_id',
                     'users.name as user_name'
                    )
            ->where('user_id','=',$users->id)
            ->get();

        foreach($users as $user) {
                $assignments = $user->assignments;
                $dept_id = $user->dept_id;

                $departmentArray[$user->dept_id] = $user->dept_name;

            }
            $users->department = $departmentArray;
            $users->dept_id = $dept_id;
        }
}

Then whenever I try to call this like:

public function index() {

    $users = DB::table('users')->get();
    $users = $this->setDepartment($users);
    return view('/users/list', [
        'users' => $users
    ]);

}

I receive

"Trying to get property 'user_id' of non-object"

Upvotes: 1

Views: 309

Answers (1)

party-ring
party-ring

Reputation: 1871

'A department has many users through assignments'

Documentation for hasManyThrough: https://laravel.com/docs/5.7/eloquent-relationships#has-many-through

Departments Model:

public function users()
{
    return $this->hasManyThrough('App\User', 'App\Assignment');
}

'Count how many users there are for each department'

Departments controller:

public function index()
{
    $departments = Department::all();

    foreach($departments as $department)
    {
         // This line returns the number of users for the department
         // Use this however you please
         $department->users->count();
    }
}

Documentation for count() found here: https://laravel.com/docs/5.7/eloquent#retrieving-aggregates

You can work out the reverse of the relationship if you need to go from users to department, but I would suggest looking into eloquent, it can be quite handy for queries such as these. Let me know if you need any further clarification.

Edit:

For access from user to department, this is one way of defining the relationships.

User Model:

// A user belongs to many departments through their assignments
public function departments()
{
    return $this->hasManyThrough('App\Department', 'App\Assignment');
}

This way you can access the departments of the user like so:

$departments = $user->departments();

And iterate through the $departments, accessing the count() as explained above.

Upvotes: 1

Related Questions