Convert sql query to Ecto query

Questions / Help
, ,
1

I have a sql query as follows

select username, email, nickname, lastname, firstname, phone 
 from a
 where NOT EXISTS
 (
 select b.tip_team_id from b
 where b.season_id = 1
 and b.round_id = 2
 and a.id = b.user_id
 );

I want to convert it into Ecto query… Can some one help me with it?

2

The main difficulty here is to write NOT EXISTS part, right? I suggest rewriting this sql query using combination of LEFT JOIN and IS NULL check.

 select username, email, nickname, lastname, firstname, phone from a
 left join b on b.user_id = a.id and b.season_id = 1 and b.round_id = 2
 where b.id is null

Then mapping this SQL to Ecto should be straightforward.

1 Like
3

Recent versions of ecto (3.4.3+ it seems) do support subqueries in where clauses.

2 Likes
4

I have a sql query as follows

> select username, email, nickname, lastname, firstname, phone 
>  from a
>  where NOT EXISTS
>  (
>  select b.tip_team_id from b
>  where b.season_id = 1
>  and b.round_id = 2
>  and a.id = b.user_id
>  );

> I want to convert it into Ecto query… Can some one help me
> with it?

something like:


from a in a_table,
  left_join b in b_table, on: a.id == b.user_id and b.round_id == 2 and b.season_id == 1,
  where: is_nil(b.id),
  select: {a.username, a.email, a.nickname, a.lastname, a.firstname, a.phone}

There are some discussions about deal with EXISTS, look them up!

1 Like
5

Recent versions of ecto (3.4.3+ it seems) do support subqueries in where clauses.

that would be very welcome but the docs for where don’t mention it. Do
you remember any reference?

1 Like
6

The docs for Ecto.Query.subquery do mention it.

2 Likes