Psql unnest in ecto

Questions / Help
1

WELL HOWDY Y’ALL!

Is it possible to replicate this query in Ecto?

SELECT unnest(ARRAY[5,10,15,20]) AS my_desired_column UNION ALL SELECT my_desired_column FROM my_desired_table

Specifically, I can’t seem to figure out how to replicate the UNION ALL :thinking:

1 Like
2

Have you seen this https://hexdocs.pm/ecto/Ecto.Query.html#union/2?

3

Thanks for your input!

The solution I came up with is to use a CTE to hold the dynamic values from the unnest:

union_query = "cte_name" |> with_cte("cte_name", as: fragment("select my_column from unnest(ARRAY[5, 10, 15]) my_column")) |> select([c], %{ my_column: fragment("my_column") }

This query is able to be unioned :tada:

2 Likes
4

Glad you managed to achieve what you wanted @CodeBumpkin

Also look at transaction and Ecto multi, to use only one trip to the database.

5

It’s been some time since this was posted but with the latest Ecto v3.9 at least there’s a way to do it without a CTE (although not too pretty either). Can’t say about previous Ecto versions.

data = [1,2,3] q1 = from(data in fragment(“select mycolumn from unnest(?::int[]) as x(mycolumn)”, ^data), select: data.mycolumn ) q2 = from(table in MyTable, select: table.mycolumn) q3 = union_all(q1, ^q2) values = Repo.all(q3)
1 Like