Users order by role name

[madurapa]

How do we pass the role name and sort the data with the query below?

    function users()
    {
        $query = User::query();
        $query->when($this->search != '', function ($query) {
            $query->where(function ($query) {
                $query->where('name', 'like', '%' . $this->search . '%')
                    ->orWhere('last_name', 'like', '%' . $this->search . '%')
                    ->orWhere('email', 'like', '%' . $this->search . '%')
                    ->orWhere('mobile', 'like', '%' . $this->search . '%')
                    ->orWhere('nic', 'like', '%' . $this->search . '%')
                    ->orWhere('cds_account', 'like', '%' . $this->search . '%');
            });
        });
        $query->when($this->status != '', function ($query) {
            $query->where('status', $this->status);
        });
        $query->when($this->role != '', function ($query) {
            $query->role($this->role);
        });
        $query->orderBy($this->sortField, $this->sortDirection);
        return $query->paginate($this->perPage);
    }

[drbyte]

In your code, where does $this->role come from?

Your question presumes that users have only one role. But this package is structured to allow users to have multiple roles.
Thus, it seems your question is really more of a Laravel-usage question, and not so much a question about using this package.

[madurapa]

$this->role is a livewire model

even if a user has multiple roles, how can we sort by role(s)?

[drbyte]

I still don't have clarity about what you're asking. The screenshot shows a filter drop down of role names. So... Do you mean "how to sort roles"? As in a collection of roles related to a model? Or do you mean "how to sort users, based on their first role name"? Or do you mean "how to filter/limit a list of users to just those belonging to a certain role"?

[madurapa]

Sorry about my bad explanation,

Here is a screenshot of the view, which is currently sorted by email in ascending order. I would like to achieve the same functionality when clicking on the 'Roles' header, sorting in ascending or descending order based on the user's first role. Please let me know if further clarification is needed.

[madurapa]

I am not sure if this is the best way to do it but I achieved the above requirement with the code below.

function users()
    {
        $query = User::query();
        $query->when($this->search != '', function ($query) {
            $query->where(function ($query) {
                $query->where('name', 'like', '%' . $this->search . '%')
                    ->orWhere('last_name', 'like', '%' . $this->search . '%')
                    ->orWhere('email', 'like', '%' . $this->search . '%')
                    ->orWhere('mobile', 'like', '%' . $this->search . '%')
                    ->orWhere('nic', 'like', '%' . $this->search . '%')
                    ->orWhere('cds_account', 'like', '%' . $this->search . '%');
            });
        });
        $query->when($this->status != '', function ($query) {
            $query->where('status', $this->status);
        });
        $query->when($this->role != '', function ($query) {
            $query->role($this->role);
        });
        $query->when($this->sortField === 'role', function ($query) {
            $query->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')
                ->join('roles', 'model_has_roles.role_id', '=', 'roles.id')
                ->select('users.*', 'roles.name as role_name')
                ->orderBy('role_name', $this->sortDirection)
                ->distinct();
        }, function ($query) {
            $query->orderBy($this->sortField, $this->sortDirection);
        });
        return $query->paginate($this->perPage);
    }

[drbyte]

Thanks for posting your solution.