Ecto select from VALUES, is it possible?

Questions / Help
,
1

How can I achieve such query in Ecto?

postgres=> SELECT * FROM (VALUES (1,2,3,4), (5,6,7,8)) AS foo (a,b,c,d);

 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4
 5 | 6 | 7 | 8
(2 rows)
1 Like
2

Right now only via Repo.query/2.

3

With compile time built fragments you can join VALUES data:

values =
    Connect.Reviewable.State.__values__()
    |> Enum.with_index(1)
    |> Enum.map_join(",", fn {field, index} -> "('#{field}',#{index})" end)
    |> (fn x -> "(VALUES #{x})" end).()

  @spec latest_review(Ecto.Queryable.t()) :: Ecto.Query.t()
  def latest_review(query) do
    from x in order_by_state(query), distinct: x.assoc_id
  end

  def order_by_state(query) do
    from x in query,
      join: y in fragment(unquote(values)),
      on: x.state == y.column1,
      order_by: y.column2
  end
1 Like
4

One workaround is to use a fragment CTE. Similar to when you need to select from an unnest

1 Like
5

Wow, nice. That will make some things much easier, however seeing that it it mostly supported then I think that fragment in from should be supported as well in near future.

2 Likes
6

Thanks! It works:

"foo"
|> with_cte("foo", as: fragment("""
SELECT * FROM (VALUES (1,2),(3,4)) AS foo (x,y)
"""))
|> select([foo], {foo.x, foo.y})
|> Repo.all()

[debug] QUERY OK source="foo" db=2.7ms queue=0.6ms idle=1721.4ms
WITH "foo" AS (SELECT * FROM (VALUES (1,2),(3,4)) AS foo (x,y)) SELECT f0."x", f0."y" FROM "foo" AS f0 []
[{1, 2}, {3, 4}]
2 Likes
7

Since Ecto 3.11.0 you can use values/2 as described here

values = [%{id: 1, text: "abc"}, %{id: 2, text: "xyz"}]
types = %{id: :integer, text: :string}

query =
  from v1 in values(values, types),
    join: v2 in values(values, types),
    on: v1.id == v2.id

Repo.all(query)
1 Like