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.