How to express Larvel subqueries to use within a whereIn-CodePudding

I'm sorry for asking somthing so simple, but I can't get the docs (https://laravel.com/docs/9.x/queries#subquery-where-clauses)

I'm writting something like a social network functionality, so I have my messages table and my users table, there's another pivot table where I store the users the user follows, and they work pretty well.

I want to represent the following SQL in Laravel's ORM

SELECT * FROM mensajes
WHERE user_id=1 
  OR user_id IN (SELECT seguido_id FROM seguidos WHERE user_id=1)

The idea is I'll get the user's posts, and also the posts from the users that the user follows.

My following solution works, but I feel it's quite dirty, and should be solved with a Subquery

// this relation returns the users the user is following, ans works correctly
$seguidos = auth()->user()->seguidos;

// I store in an array the ids of the followed users
$seg = [];
foreach ($seguidos as $s) {
  array_push($seg, $s->id);
}
array_push($seg, auth()->user()->id);

// Then I retrieve all the messages from the users ids (including self user)
$this->mensajes = Mensaje::whereIn('user_id', $seg)
  ->orderBy('created_at', 'desc')
  ->get();

I'd like to change everything to use subqueries, but I don't get it

$this->mensajes = Mensaje::where('user_id', auth()->user()->id)
  ->orWhereIn('user_id', function($query) {
    // ... what goes here?
    // $query = auth()->user()->seguidos->select('id');
    // ???? This doesn't work, of course
    }
  ->orderBy('created_at', 'desc')
  ->get();

CodePudding user response：

You can simply construct the raw query as you have done with the SQL.

->orWhereIn('user_id', function($query) {
        $query->select('seguido_id')
            ->from('seguidos')
            ->where('seguidos.user_id', auth()->user()->id);
});

But normally sub queries have a relationship between the primary SQL query with the sub query, you don't have this here and instead of doing one query with a sub query, you can quite simply write it as two queries. If you are not calling this multiple times in a single request, one vs two queries is insignificant performance optimization in my opinion.

Mensaje::where('user_id', auth()->user()->id)
    ->orWhereIn(
        'user_id',
        Seguidos::where('user_id', auth()->user()->id)->pluck('seguido_id'),
);