This does basically the same thing as UNION in a single query.
from a in SchemaA, join: b in SchemaB on: fragment("true")
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
0
michalmuskala
FWIW in Ecto master there’s support for the filter expressions. Using them, this could be achieved with a simpler query:
{account, inflow, outflow} =
Repo.one(from a in App.Account,
left_join: t in App.Transaction,
on: t.account_id == a.id and t.user_id == ^current_user.id and not t.deleted,
where: a.id == ^id,
group_by: a.id,
select: {a, filter(sum(t.amount), t.type == "inflow"), filter(sum(t.amount), t.type == "outflow")})
2
peerreynders
So right now
select: {a, {fragment("SUM(CASE WHEN ? = 'inflow' THEN ? ELSE 0 END)", t.type, t.amount), fragment("SUM(CASE WHEN ? = 'outflow' THEN ? ELSE 0 END)", t.type, t.amount)}}
or
select: {a, {fragment("SUM(?) FILTER (WHERE ? = 'inflow')", t.amount, t.type), fragment("SUM(?) FILTER (WHERE ? = 'outflow')", t.amount, t.type)}}
should work.
Example:
alias MusicDB.{Repo,Album,Track}
import Ecto.Query
artist_id = 2
query = from(ar in Artist, [
join: a in Album, on: ar.id == a.artist_id,
join: t in Track, on: a.id == t.album_id,
where: ar.id == ^artist_id,
group_by: ar.id,
select: {ar, {fragment("SUM(CASE WHEN ? = ? THEN ? ELSE 0 END)", t.album_id, 3, t.duration), fragment("SUM(CASE WHEN ? = ? THEN ? ELSE 0 END)", t.album_id, 4, t.duration)}}
])
Repo.one(query)
iex(5)> Repo.one(query)
14:57:13.174 [debug] QUERY OK source="artists" db=4.0ms decode=2.7ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at", SUM(CASE WHEN t2."album_id" = 3 THEN t2."duration" ELSE 0 END), SUM(CASE WHEN t2."album_id" = 4 THEN t2."duration" ELSE 0 END) FROM "artists" AS a0 INNER JOIN "albums" AS a1 ON a0."id" = a1."artist_id" INNER JOIN "tracks" AS t2 ON a1."id" = t2."album_id" WHERE (a0."id" = $1) GROUP BY a0."id" [2]
{%MusicDB.Artist{
__meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
birth_date: nil,
death_date: nil,
id: 2,
inserted_at: ~N[2018-06-16 20:29:41.481934],
name: "Bill Evans",
tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
updated_at: ~N[2018-06-16 20:29:41.481940]
}, {3456, 2540}}
iex(6)>
That essentially results in a CROSS JOIN, not a UNION:
iex(1)> Repo.all(from(a in Artist, [
...(1)> join: b in Artist, on: fragment("true"),
...(1)> select: {a.name, b.name}
...(1)> ]))
15:36:31.196 [debug] QUERY OK source="artists" db=1.9ms
SELECT a0."name", a1."name" FROM "artists" AS a0 INNER JOIN "artists" AS a1 ON true []
[
{"Miles Davis", "Miles Davis"},
{"Miles Davis", "Bill Evans"},
{"Miles Davis", "Bobby Hutcherson"},
{"Bill Evans", "Miles Davis"},
{"Bill Evans", "Bill Evans"},
{"Bill Evans", "Bobby Hutcherson"},
{"Bobby Hutcherson", "Miles Davis"},
{"Bobby Hutcherson", "Bill Evans"},
{"Bobby Hutcherson", "Bobby Hutcherson"}
]
iex(2)> Repo.all(from(a in Artist, [
...(2)> cross_join: b in Artist,
...(2)> select: {a.name, b.name}
...(2)> ]))
15:36:31.204 [debug] QUERY OK source="artists" db=1.8ms
SELECT a0."name", a1."name" FROM "artists" AS a0 CROSS JOIN "artists" AS a1 []
[
{"Miles Davis", "Miles Davis"},
{"Miles Davis", "Bill Evans"},
{"Miles Davis", "Bobby Hutcherson"},
{"Bill Evans", "Miles Davis"},
{"Bill Evans", "Bill Evans"},
{"Bill Evans", "Bobby Hutcherson"},
{"Bobby Hutcherson", "Miles Davis"},
{"Bobby Hutcherson", "Bill Evans"},
{"Bobby Hutcherson", "Bobby Hutcherson"}
]
iex(3)>
1