Laravel Spartie get users with their roles

Question

I'm trying to create a view for displaying a list of users in the system with their roles in a table but I'm only getting one user returned despite having one super admin and several admins

Here is the code I'm using

      $data = User::join('model_has_roles','model_has_roles.model_id','users.id')
          ->join('roles','model_has_roles.role_id','roles.id')
          ->join('user_profiles','user_profiles.user_id','users.id')
          ->select('users.id', 'title', DB::raw("CONCAT(user_profiles.fname,
          ' ', user_profiles.mname,' ', user_profiles.sname) As name"), 'email',
          'gender', 'roles.name As role')
          ->where('roles.id',8)->orWhere('roles.id',9)
          ->get();`

this is what I'm getting in the table

I suspect that the first join is causing the issue but am unsure. Since I'm using server-side data tables, I also tried doing this:

      ...,
            {
                data: "id",
                render:function(data){
                    return(
                        user(data).getRoleNames()
                    );
                },
                name: "role"
            }, ...

But this give s me the following error in console

      admins:529  Uncaught ReferenceError: user is not defined
at render (admins:529:25)
at jquery.dataTables.min.js:115:15
at Ga.b.fnGetData (jquery.dataTables.min.js:28:234)
at T (jquery.dataTables.min.js:33:248)
at ab (jquery.dataTables.min.js:38:118)
at ia (jquery.dataTables.min.js:32:467)
at Qb (jquery.dataTables.min.js:53:234)
at jquery.dataTables.min.js:50:337
at Object.g [as success] (jquery.dataTables.min.js:49:73)
at c (jquery.min.js:2:28327)

Any idea on how I can achieve this using custom code on the user's model or spartie functions?

umm, curious question, any good reason to use `QueryBuilder` instead of [tag:eloquent] for the query? afaik spatie prefers doing things using [tag:eloquent]. — Bagus Tesa, Jul 08 '23 at 07:58
@BagusTesa I'm not so good with eloquent hence the use of `QueryBuilder` Could you pleasegive me a clue on how to use eloquent for this — The Only Smart Boy, Jul 08 '23 at 08:16
@OMR, yes I am sure because the users are seeded using factory — The Only Smart Boy, Jul 08 '23 at 08:32
After close scrutiny, I've realized that the admins were having no profiles instead the super admin had multiple profiles. — The Only Smart Boy, Jul 08 '23 at 08:39

score 0 · Accepted Answer · answered Jul 10 '23 at 06:38

0

This problem occurs if any of the join tables is empty for any of the data in the primary table in this case it's the Users table. I solved the issue by solving for the missing data in the user_profiles table.

answered Jul 10 '23 at 06:38

The Only Smart Boy

578
19
39

Laravel Spartie get users with their roles

1 Answers1