I could use some assistance in formatting a query

I have a query that I'm struggling to get to bracket correctly, query appears at the end of this post

I have three relations:

User
User->offices()
User->roles()

The User has a default office id stored in the office_id field and a ManytoMany relationship accessed via the User->offices() relation and ManytoMany relationship accessed via the User->roles() relation.

to pseudocode

if $myofficeid > 0 ( find user where office_id = $myofficeid OR user->offices().id = $myofficeid) AND user->roles() has name = "High Tech Coordinator)

what I'm getting now is effectively

if $myofficeid > 0  find user where office_id = $myofficeid OR (user->offices().id = $myofficeid and user->roles() has name = "High Tech Coordinator)

I assumed that trying to place both of the office related queries in the ->when closure would bracket them together.

$hightech = ['0' => 'Please Select'] + User::where('status','=',1)
            ->when($myofficeid > 0 , function($query) use($myofficeid){
                $query->whereHas('offices', function($query) use ($myofficeid){
                    $query->where('offices.id', '=', $myofficeid);
                })
                     ->orWhere('office_id','=',$myofficeid);
            })
            ->whereHas('roles', function ($query){
                   $query->where('name','=', 'High Tech Coordinator');
            })
            ->orderBy('name')->pluck('name','id')->toArray();

jlrdw

7 years ago

Level 75

Looks like you could use query scopes more efficiently. I usually have a helper class that in the background figures out and returns who can do what.

See

https://laracasts.com/discuss/channels/laravel/using-laravel-policy-to-filter-eloquent-query

and

https://laracasts.com/discuss/channels/general-discussion/authorization-policies-and-reducing-the-repitition

But scopes would reduce clutter, and a class in the background could just return to the scope if someone can or cannot do something.

I have switched to scopes now since snapey suggested.

    public function scopegetPets($query, $petsearch = '')
    {
        $petsearch = $petsearch . "%";
        $query = Pet::where('petname', 'like', $petsearch);
        if (ChkAuth::userRole('admin') === false) {
            $userid = Auth::user()->id;
            $query->where('ownerid', '=', $userid);
        }
        $results = $query->orderBy('petname', 'asc')->paginate(5);
        return $results;
    }

If admin show all, otherwise just show the users data.

Not saying do it exactly like this, but you could have a class to call that works in the background to resolve this stuff. I just made a helper class, but you can decide for yourself the easier way.

A couple more:

https://laracasts.com/discuss/channels/laravel/permissions-and-roles

https://stackoverflow.com/questions/43901719/laravel-middleware-with-multiple-roles

Dalma

7 years ago

Best Answer

Level 6

I found my solution. I needed to wrap the inital or clauses in a closure

        $hightech = ['0' => 'Please Select'] +
            User::where(function ($query) use ($myofficeid) {
                $query->where('office_id','=', $myofficeid)
                      ->orwhereHas('offices', function ($query) use ($myofficeid){
                          $query->where('offices.id', '=', $myofficeid);
                      });
            })->where('status','=', 1)
            ->whereHas('roles', function ($query){
                $query->where('name','=', 'High Tech Coordinator');
            })->orderBy('name')->pluck('name','id')->toArray();

Please or to participate in this conversation.