i forgot to mention, the resulting query gives me 36, instead of 31
get all users that are not friends with logged user
i want to get all the users in my app that are not connected (or are friends, in a friendship relation) to the logged user
this are my relation methods on the user model (ugly named columns and tables, not my fault :p):
public function userConnections()
{
return $this->belongsToMany('Broker', 'brokers_rel_brokers', 'id_broker', 'id_broker2')
->where('brokers_rel_brokers.estatus', 1);
}
public function connectedTo()
{
return $this->belongsToMany('Broker', 'brokers_rel_brokers', 'id_broker2', 'id_broker')
->where('brokers_rel_brokers.estatus', 1);
}
public function scopeForNetworkView($query)
{
$userId = Auth::id();
return $query
->where('id_broker', '<>', $userId)
->whereDoesntHave('connectedTo', function ($query) use($userId) {
$query->where('brokers.id_broker', $userId);
})
->whereDoesntHave('userConnections', function ($query) use($userId) {
$query->where('brokers.id_broker', $userId);
});
}
this is the SQL query i get (using count because i was debugging the query) using that query scope scopeForNetworkView:
SELECT
count(*)
FROM
`brokers`
WHERE
`id_broker` <> 3
AND NOT EXISTS( SELECT
*
FROM
`brokers` AS `laravel_reserved_0`
INNER JOIN
`brokers_rel_brokers` ON `laravel_reserved_0`.`id_broker` = `brokers_rel_brokers`.`id_broker`
WHERE
`brokers_rel_brokers`.`id_broker2` = `brokers`.`id_broker`
AND `brokers`.`id_broker` = 3
AND `brokers_rel_brokers`.`estatus` = 1)
AND NOT EXISTS( SELECT
*
FROM
`brokers` AS `laravel_reserved_1`
INNER JOIN
`brokers_rel_brokers` ON `laravel_reserved_1`.`id_broker` = `brokers_rel_brokers`.`id_broker2`
WHERE
`brokers_rel_brokers`.`id_broker` = `brokers`.`id_broker`
AND `brokers`.`id_broker` = 3
AND `brokers_rel_brokers`.`estatus` = 1)
brokers = users btw :p.
first of all, i should get 31 for that query, there are 37 users in the DB, logged user has 5 connections, and logged user must not show on the results, i of course could use the alias that laravel produce (laravel_reserved_0 and laravel_reserved_1), it actually works!, but then my query scope will look weird, is there a way so i can assign an alias? or a way that i can tell the query builder to use the generated alias?, i know i can use some other query builder methods, but i want to keep this as simple as posible using 'eloquent behavior'.
please help :(
Please or to participate in this conversation.