CODEWITHSUNDEEP
postgresqlelixirecto
user13528444
user13528444

Reputation: 76

Convert LEFT JOIN query to Ecto

I have some queries I need migrate to Ecto and for maintainability reasons, I'd rather not just wrap them in a fragment and call it a day.

They have a lot of LEFT JOINs in them and as I understand from this answer, a left_join in Ecto does a LEFT OUTER JOIN by default. I can't seem to figure out how to specify to Ecto that I want a LEFT INNER JOIN, which is the default behavior for a LEFT JOIN in Postgresql.

To look at a toy example, let's say posts in our database can be either anonymous or they can have a creator. I have a query to get just enough info to make a post preview, but I only want non-anonymous posts to be included:

SELECT
  p.id,
  p.title,
  p.body,
  u.name AS creator_name,
  u.avatar AS creator_avatar,
FROM posts p
LEFT JOIN users u ON p.creator_id = u.id;

I would translate that into Ecto as:

nonanonymous_posts =
  from p in Post,
    left_join: u in User, on: p.creator_id == u.id,
    select: [p.id, p.title, p.body, u.name, u.avatar]

and Ecto spits out

SELECT
  t0."id",
  t0."title",
  t0."body",
  t1."name" AS creator_name,
  t1."avatar" AS creator_avatar,
FROM "posts" AS t0
LEFT OUTER JOIN "users" as t1 ON t0."creator_id" = t1."id";

which will give back anonymous posts as well.

Upvotes: 0

Views: 810

Answers (1)

Hauleth
Hauleth

Reputation: 23556

There is no such thing as LEFT INNER JOIN. There is only INNER JOIN and LEFT [OUTER] JOIN (OUTER part is optional, as LEFT JOIN must be outer join). So what you want is just :join or :inner_join in your Ecto query.

Upvotes: 1

Related Questions