Filtering items by relation using knex query

Question

I'm trying to filter items by relation using a knex query. I'm almost there (I think) but struggling a little and could use some help as this is new to me.

I have a list of users who are following people and have followers. I'm trying to return a list of users who I'm not already following. Below is my code so far:

const users = await knex("users-permissions_user").whereNotExists(
  function () {
    this.select("*")
      .from("users_followers__users_followings")
      .where("user_id", "users-permissions_user.id")
      .where("follower_id", id);
  }
);

This returns a list of users who currently have no followers and users where I'm the only follower. Any users who I follow and also have more followers are still returned. I thought like would achieve this type of filter but I must be doing it wrong.

Here is how the table for the followers/following relation appears in my db:

And here is the data that would be returned from the above query:

[
  {
    "id": "138",
    "followers": [
      {
        "id": "143"
      }
    ]
  },
  {
    "id": "140",
    "followers": [
      {
        "id": "160"
      },
      {
        "id": "136"
      }
    ]
  },
  {
    "id": "135",
    "followers": []
  },
  {
    "id": "136",
    "followers": []
  }
]

As you can see, users with no followers are returned as are users who I'm not already following but users who have multiple followers, including me (ID 160), are returned when they should be omitted.

Any advice would be greatly appreciated!

Answer 1

So the reason that users like 140 are being returned even though they are following you (160) is because they are following at least one other person who isn't you, which means your where clause will match them. If you want to return only users who are not following you, you could achieve this by replacing your left join and where clause with a where not exists clause. In knex that would look something like:

qb.whereNotExists(function() {
     this.select(1)
    .from('users_followers__users_followings')
    .where('users_followers__users_followings.user_id', knex.ref('users-permissions_user.id'))
    .where('users_followers__users_followings.follower_id', id);
});