Laravel Query with Joins issue

Question

In my laravel application, I have tables to store user roles, users and inquiries

I have three different user roles.

1/ user (role_id is 3)
2/ regional_admin (role_id is 2)
3/ admin (role_id is 1)

Regional admins only can access to the inquiries submitted by the same users from the same region.

If a regional admin has access to an inquiry submitted from a different region user, it should display 419 error.

I'm saving the user's region as, region_id in the Users table

So far I have the following in my inquiry controller's show method,

public function show($id)

    {

        if(!empty($id)){

        
        //$inquery = Inquery::find($id);

        $inquery = DB::table('users')
            ->join('inqueries', 'users.id', '=', 'inqueries.user_id')
            ->select('users.first_name as name','users.email as email', 'inqueries.*')
            ->where('inqueries.id','=',$id)
            ->first();

        //dd($inquery);   

            if(empty($inquery)){
                
                abort(404, 'Nothing Here');
            }

            else{

                return view('admins.inqueries.show',compact('inquery'));
            }

        }
        
        else{

            abort(404, 'Nothing Here');

        }

    }

This way I can display all the inquiries but I'm struggling to restrict regional admins accessing to messages from other region users.

My show inquiry URL looks like this,

http://test.site/admins/inqueries/1

1 is inquiry id

Assume this has been submitted by user A who is from region 1, Then only regional admin(s) from region 1 and Super Admin should be able to access this...

Any outside regional admin trying to access this, should be shown a 419 page.

I tried doing this by matching auth user's region id to the inquiry sender's region id, But I'm unable to write the query according to that...

Answer 1

I would do something like this inside the show method,

public function show($id)

    {

        if(!empty($id)){

            $inquery = DB::table('users')
                ->join('inqueries', 'users.id', '=', 'inqueries.user_id')
                ->select('users.first_name as name','users.email as email', 'inqueries.*')
                ->where('inqueries.id','=',$id)
                ->first();

            if(empty($inquery)){
                    
                abort(404, 'Nothing Here');
            }    
            
            $userInquery = $inquery->user_id;

            $get_userInquiryRegion=User::where('id','=',$userInquery)->pluck('region_id')->first();

            if(Auth::user()->role_id=='1'){


                    return view('admins.inqueries.show',compact('inquery'));
                
            } 
            
            else if(Auth::user()->region_id==$get_userInquiryRegion){

                    return view('admins.inqueries.show',compact('inquery'));
            
            } 
            
            else{

                abort (403);
            }


        }
        
        else{

            abort(404, 'Nothing Here');

        }

    }

Answer 2

I think you can do something like this

$inquery = Inquery::find($id);
        $userInquery = $inquery->user;
        if (/*is regional admin &&*/ \auth()->user()->region_id == $userInquery->region_id )
        {
            // do you region admin login
        }
        else
            abort(419, 'abort message');