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.