Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

cesar's avatar

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 :(

0 likes
1 reply
cesar's avatar

i forgot to mention, the resulting query gives me 36, instead of 31

Please or to participate in this conversation.