KP123

KP123

Ecto: fetch all records not related in join table

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?

Marked As Solved

tfwright

tfwright

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.

Where Next?

Popular in Questions Top

chokchit
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2733ms. You can configure how long re...
New
Tee
can someone please explain to me how Enum.reduce works with maps
New
lessless
I believe there are people here who are dealing with CSV files import on the daily basis, and since Excel is a really popular tool there ...
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
stefanchrobot
What’s the safe way to decode a JSON string into a struct? I want to avoid calling String.to_atom. Jason.decode can give me a map with st...
New
SoCreat
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
New
srinivasu
How to handle excepions in elixir? Suppose i have A, B, C ,D, E modules. and each module has get() function. A.get() method will call t...
New
komlanvi
Hi everyone, I was playing with phoenix liveView but I run into an issue. I have a form and want to validate each input text when the te...
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
New
jaysoifer
Is there a way to rollback a specific migration and only that one (“skipping” all the other ones)? Would mix ecto.rollback -v 200809061...
New

Other popular topics Top

lessless
I believe there are people here who are dealing with CSV files import on the daily basis, and since Excel is a really popular tool there ...
New
danschultzer
None of the current solutions worked well for me, so I went ahead and built a user management system from scratch. This project took far...
548 29703 241
New
jononomo
I am trying to figure out how Mix knows whether the environment is test, dev, or prod – where is this set? Thanks.
New
belgoros
I’m not a pro in using Regex and can’t figure out why the following behaviour happens, especially if we take into account the difference ...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
saif
Hello everyone, Long time lurker first time poster here. I’ve recently begun working on Elixir full-time again! :raised_hands: It’s been...
New
rms.mrcs
Hi, I need to transform a list of numbers into a map where the keys are the indexes and the values are the original values of the list. ...
New
Qqwy
Update: How to use the Blogs & Podcasts section You can post links to your blog posts or podcasts either in one of the Official Blog...
3271 127536 1222
New
hariharasudhan94
Lets say I have map like this fetching from my database %{"_id" => #BSON.ObjectId<58eb1a7a9ad169198c3dXXXX>, "email" => ...
New
JakeBecker
TL;DR: I’ve just released an implementation of Microsoft’s IDE-independent Language Server Protocol for Elixir. It adds language support ...
1144 54250 245
New

We're in Beta

About us Mission Statement