How do I subquery Laravel based on this information?

Question

I have the tables:

Notifications:

users:

... And I got the following code that’s in my controller:

public function allRelations($internalClientId)
{              
        
  $internalClient = InternalClient::find($internalClientId);
        
  if(empty($internalClient))
  {
     return response()->json(['error' => 'Internal Client not found!'], 404);
  } 

  $notifications = InternalClient::join('notifications', 'notifications.internal_client_id','=','internal_client_id')         
  ->select(                                               
           'notifications.id AS notification_id',                                        
           'notifications.from_user_id AS from_user_id',                                       
           'notifications.to_user_id AS to_user_id', 
           'notifications.title AS notification_title', 
           'notifications.message AS notification_message',
           'notifications.checked AS notification_checked'
          )                                                          
          ->where('internal_client_id',$internalClientId) 
          ->groupBy('notifications.id')     
          ->get();

   return response()->json($notifications, 201);
}

At the moment this code returns me this:

So far so good. Only that I would like to return the 'name' (that’s on the table users) corresponding to those ID’s who are in the fields from_user_id and to_user_id on the table Notifications (which are Foreign key of table users users). I believe that for this it will be necessary to make a subquery. But I never did it in the Laravel.

Could someone help me ?

Answer 1

I was able to solve using the raw class DB.

I imported the class at the top of the file like this: use Illuminate Support Facades DB; //Calling class DB

And the code of the method went like this:

public function allRelations($internalClientId)
{              
        
   $internalClient = InternalClient::find($internalClientId);
        
   if(empty($internalClient))
   {
      return response()->json(['error' => 'Internal Client not found!'], 404);
   } 

   $notifications = InternalClient::join('notifications', 'notifications.internal_client_id','=','internal_client_id')          
   ->select(                                               
             'notifications.id AS notification_id',                                        
             'notifications.from_user_id AS from_user_id', 
              DB::raw("(SELECT name FROM users WHERE id = from_user_id) AS from_user_name"),           
              'notifications.to_user_id AS to_user_id', 
              DB::raw("(SELECT name FROM users WHERE id = to_user_id) AS to_user_name"),
              'notifications.title AS notification_title', 
              'notifications.message AS notification_message',
              'notifications.checked AS notification_checked'                                         
            )                                                          
            ->where('internal_client_id',$internalClientId) 
            ->groupBy('notifications.id')     
            ->get();

   return response()->json($notifications, 201);
}

The return went like this:

Although I thought I’d gotten a bit ugly (and left [I think] a bit of the Laravel premise), it was exactly what I needed. I hope I can help those who need it most!