Nested subqueries with Ecto

Question

Using Ecto v2.2.6, Phoenix 1.3

I have a Blog app with a Newsfeed. It works like this:

• Users can submit Posts.
• Users can follow other Users.
• When a user submits a Post, an item in the Newsfeed table is added.
• Users can see a newsfeed of Posts that have been submitted by Users they are following.

I want to use Ecto.Query to get a list of newsfeed items from users that a given user is following.

---

Quick background. Here are the objects:

User

mix phx.gen.json Accounts User users email:string password:string

Post

mix phx.gen.json Content Post posts title:string content:string user_id:references:users

(users and posts have a has_many: and belongs_to: relationship.)

Follow

mix phx.gen.json Accounts Follow follows following_id:references:users followed_id:references:users

(When user A follows user B, a new Follow entry is created where following_id points to A, and followed_id points to B.)

Newsfeed

mix phx.gen.json Content Newsfeeditem newsfeeditems type:string, user_id:integer, content:string

---

Now I want to query this stuff. For me to get a list of Newsfeeditems for a given user, it's easy:

import Ecto.Query

query =
  from n in Newsfeeditem,
    where: n.user_id == ^user_id

Let's say I'm user 1, and I am following users 2, 3 and 4. There are three entries in the follows table. To get all the corresponding newsfeeditems for those users, the query would look something like this:

query =
  from n in Newsfeeditem,
    where: n.user1_id in [2,3,4]

I want to make it dynamic. Here's where I am lost. I would like to do something that resembles this:

subquery =
  from f in Follow,
    where: f.following_id == 1,
    select: f.follower_id

query =
  from n in Newsfeeditem,
    where: n.user_id in (Repo.all(subquery))

Obviously this doesn't work, but I'm not sure how to structure these things correctly.

How can I select this via subquery? (Note I'm looking for the subquery solution specifically, but bonus points if there is a better way)

Answer 1

Subquerys are currently not allowed in where clauses; the documentation recommends using a JOIN instead. Your query can be converted to a JOIN quite easily. I haven't tested it but this should work:

query =
  from f in Follow,
    where: f.following_id == 1,
    join: n in Newsfeeditem,
    on: n.user_id == f.follower_id,
    select: n