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)
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)
Right now only via Repo.query/2
.
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
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.
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}]