Convert query to Ecto

Questions / Help
1

Hi,

I’m trying to convert the following query to Ecto:

select * from unnest(ARRAY['one', 'two', 'not-there']) except select id from table;

I haven’t been able to get the unnest fragment to compile. Any help?

2

Unfortunately you cannot query from fragment with Ecto query API.

3

Yeah. I’m trying with a raw SQL query, but not sure how to cast an array of strings in Elixir to a postgres array.

4

Indeed ecto does not support a function as a from_item (see https://www.postgresql.org/docs/12/sql-select.html).

You may put an arbitrary table_name in the from like from(u in "users"), and ecto will treat it as a relation name:

iex(17)> q = from(a in "unnest(ARRAY['one', 'two', 'not-there'])", select: field(a, ^:*))
#Ecto.Query<from u0 in "unnest(ARRAY['one', 'two', 'not-there'])", select: u0.*>
iex(18)> Repo.to_sql(:all, q)
{"SELECT u0.\"*\" FROM \"unnest(ARRAY['one', 'two', 'not-there'])\" AS u0", []}
iex(19)> Repo.all(q)
[debug] QUERY ERROR source="unnest(ARRAY['one', 'two', 'not-there'])" db=0.0ms queue=1.1ms idle=1932.6ms
SELECT u0."*" FROM "unnest(ARRAY['one', 'two', 'not-there'])" AS u0 []
** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "unnest(ARRAY['one', 'two', 'not-there'])" does not exist

    query: SELECT u0."*" FROM "unnest(ARRAY['one', 'two', 'not-there'])" AS u0
    (ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:545: Ecto.Adapters.SQL.execute/5
    (ecto 3.4.0) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
    (ecto 3.4.0) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3

You may use raw sql with Ecto.Adapters.SQL.query!

iex(12)> Ecto.Adapters.SQL.query!(Repo, "select * from unnest(ARRAY['one', 'two', 'not-there']) except select id from tests")
[debug] QUERY OK db=0.9ms queue=0.7ms idle=1012.9ms
select * from unnest(ARRAY['one', 'two', 'not-there']) except select id from tests []
%Postgrex.Result{
  columns: ["unnest"],
  command: :select,
  connection_id: 14817,
  messages: [],
  num_rows: 3,
  rows: [["one"], ["two"], ["not-there"]]
}

It’s rather uncommon to use a function that returns rows as main source (YMMV).

2 Likes
5

I ended up going with

    Ecto.Adapters.SQL.query!(
      Repo,
      "select * from unnest($1::varchar[]) except select id from table",
      [["one", "two", "not-there"]]
    )

Thanks for the help!

2 Likes
6

If your array and the amount of unique IDs is reasonably small then you can compete difference in app. Otherwise this is probably the only solution for now.