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
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
Popular in Questions
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2733ms. You can configure how long re...
New
can someone please explain to me how Enum.reduce works with maps
New
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
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
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
i’m a new one to elixir
which editor can i use
vs code? or atom?
Thanks! :smiley:
New
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
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
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
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
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
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...
New
I am trying to figure out how Mix knows whether the environment is test, dev, or prod – where is this set?
Thanks.
New
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
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
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
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
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...
New
Lets say I have map like this fetching from my database
%{"_id" => #BSON.ObjectId<58eb1a7a9ad169198c3dXXXX>, "email" => ...
New
TL;DR: I’ve just released an implementation of Microsoft’s IDE-independent Language Server Protocol for Elixir. It adds language support ...
New
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #hex










