CODEWITHSUNDEEP
postgresqlelixirecto
heartmo
heartmo

Reputation: 582

How to join and filter between 3 tables with ecto query expressions

I have 3 schemas: Post, Author, Assistant.

Post belongs_to :author

Author has_many :posts and has_one :assistant

Assistant belongs_to :author

I want to build a query that only gets Posts where the Author has an Assistant.

So far, I've only been able to join the Author to the Post. I can't figure out how to query Posts based on the presence of an Author's Assistant.

Post
|> join(:inner, [p], a in assoc(p, :author))
|> Repo.all()

Edit 1

I think I've made some progress and this query seems to be working with the couple of records I have in the db right now, but not sure if it's the conventional way.

Post
|> join(:inner, [p], a in assoc(p, :author), as: :author)
|> join(:left, [author: a], asst in assoc(a, :assistant), as: :assistant)
|> where([assistant: asst], not is_nil(asst.id)))
|> Repo.all()

Upvotes: 2

Views: 2792

Answers (2)

Mike Quinlan
Mike Quinlan

Reputation: 2882

I don't think a left join is necessary for this scenario. You should be able to inner join on all of the tables because an inner join will indicate the presence of an assistant and rule out any possibility of getting an author without an assistant. You should be able to write something like this:

query = from(p in Post,
  join: a in Author,
  on: a.id == p.author_id,
  join: ast in Assistant,
  on: ast.author_id == a.id,
  # See notes below about the distinct
  distinct: p.id
)

Repo.all(query)

Because you inner join on all of the tables it indicates that a post must have an author and an author must have an assistant to be a resulting row in this query. If you wanted to do something like figure out all of the posts that have an author with no assistant you would want to left join.

Note about distinct:

I put the distinct clause in case you have multiple assistants assigned to an author in your database (which would indicate bad data) and posts in the result set would be duplicated. This is because a has_one relationship is just a constrained has_many. If you have a unique index on the author_id column in the assistants table, you do not need the distinct clause. If you do not have a unique index you can write this in a migration:

create unique_index("assistants", :author_id)

You should probably do this for all of your has_one relationships.

Cheers!

Upvotes: 3

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

This might be an overkill in this particular case, but it’s always easier to decouple such queries into subqueries.

authors_with_assistants =
  from a in Author, where: not is_nil(a.assistant_id)

or 

authors_with_assistants =
  from a in Author,
  left_join: asst in assoc(a, :assistant)

and

posts =
  from p in Post,
    join: a in subquery(authors_with_assistants),
    on: p.author_id == a.id

Repo.all(posts)

Also, chaining joins with a condition on the latter would also work (meaning your query in Edit1 section looks fine.)

Upvotes: 2

Related Questions