Eloquent ORM select with belongToMany relation

Question

Usuario Table

usuarioid | nome
 1        | joão
 2        | jose
 3        | maria

Rede Table

redeid | nome
 1     | Subway
 2     | McDonald's
 3     | Burguer King

Table Rede_usuario

id | redeid | usuarioid
 1 |    1   |    2
 2 |    2   |    1
 2 |    2   |    3
 3 |    3   |    3

the user can list only users who are part of the same network as him, I need a select so:

SELECT 
      usuario.* 
INNER JOIN 
      rede on rede.redeid = rede_usuario.redeid 
INNER JOIN 
      rede_usuario on rede_usuario.usuarioid = usuario.usuarioid
WHERE 
      usuario.usuarioid = ? and rede.redeId in (?, ?)

I tried to ride that way with the eloquent:

Usuario::select('usuario.*')
         ->where('usuarioid','2')// usuario maria passou o id do jose (2)
         ->with(['rede' => function( $query ) {
                $query->whereIn('rede.redeId', [2,3])// a rede da maria é 2 e 3
         }])->first();// não deveria retornar infomação do jose (id 2) mas retorna =\

but it did not work, the user maria could only list maria and john, but could not list Jose because Jose is not part of networks 2 and 3, the query not of the error but it lists users outside of its network, when the user logs into the system I get the network of it, then in this query the user informs the id of the user he wants to see and the network he belongs to, and only returns result with the above rule (from the same network).

Note: when the user logs into the system automatically lists only the users of his network, but for security reasons I want to implement this.

Answer 1

Assuming you’re using the Eloquent ORM, you can get the desired query with joins:

$query = DB::table('table')->select('usuario.*')
            ->where('usuarioid','2')
            ->join("rede",function($join){
                $join->on("rede.id","=","rede_usuario.redeid");
            })
            ->join("usuario",function($join){
                $join->on("rede_usuario.usuarioid","=","usuario.usuarioid");
            })->toSql();

The result of the above query would be:

select usuario.* from `table` 
inner join `rede` on `rede`.`id` = `rede_usuario`.`redeid` 
inner join `usuario` on `rede_usuario`.`usuarioid` = `usuario`.`usuarioid` 
where `usuarioid` = ?