Eloquent ORM select with respect toToToMany

0

User table

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

Network Table

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

User_Network table

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

The user can only list the users that are part of the same network as him, I need a select like this:

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 mount this way with 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, user maria could only list maria and joão , but could not list jose > because jose is not part of networks 2 and 3, the query does not error but it lists users outside of your network, when the user logs on the system I take his network, then in that 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 in the system automatically lists only the users of his network, but for security reasons I want to implement this.

    
asked by anonymous 24.08.2018 / 00:23

1 answer

0

Assuming you are using 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' = ?
    
24.08.2018 / 03:11