Ecto: fetch all records not related in join table

Questions / Help
, , ,
1

Hello, I have a system with users that can subscribe to other users. This is being modeled using many_to_many, the schemas look as below:

schema “users” do
    many_to_many :subscribers, User,
        join_through: UserSubscriber, 
        join_keys: [target_id: :id, subscriber_id: :id]

    many_to_many :subscriptions, User,
        join_through: UserSubscriber, 
        join_keys: [subscriber_id: :id, target_id: :id]
end

schema “users_subscribers” do
    belongs_to :target, User
    belongs_to :subscriber, User
end

My question is: Given a user, Bob, how can I fetch all users that Bob is not subscribed to? I wish to present Bob with a form where he may search for users he’s not yet subscribed to. Is there an easy way to query this in ecto or is raw sql required?

2

There are several ways. Here is one using a subquery:

subscribed_id_query = bob |> Ecto.assoc(:subscribers) |> select(:id)
where(User, [u], u.id not in subquery(subscribed_id_query))

This is the simplest query that comes to mind, not necessarily the most optimized. If you have specific SQL you would like to generate you should include that in your post.

1 Like
3

Works how I need it to. Thanks