Keyword queries are used to dynamically compose pieces of the query together. The idea for your case is to use a case expression compose the correct where part. However keyword queries need a binding list.
An example of converting a query expression to a keyword query:
def query_expression() do
# as a query expression
from(r in Artist,
left_join: a in Album,
on: r.id == a.artist_id,
left_join: t in Track,
on: a.id == t.album_id,
order_by: [asc: r.id, asc: a.id, asc: t.index],
select: %{
artist_id: r.id,
name: r.name,
album_id: a.id,
album_title: a.title,
track_id: t.id,
title: t.title
}
)
end
def query_keyword() do
# as a keyword query (without pipelining)
# the query is being **composed** step by step
# Note how the binding list changes with each step
# [r]
# [r,a]
# [r,a,t]
# Being first "Artist" becomes the first in the binding list
# [r] so "r" refers to "Artist"
# [x] so "x" refers to "Artist"
#
query = Artist
# Album becomes the second binding
# [x,y] "x" is "Artist"", "y" is "Album"
#
query = join(query, :left, [r], a in Album, on: r.id == a.artist_id)
# Track becomes the third binding
# [a,b,c] "a" is "Artist", "b" is "Album", "c" is "Track"
#
query = join(query, :left, [r, a], t in Track, on: a.id == t.album_id)
query = order_by(query, [r, a, t], asc: r.id, asc: a.id, asc: t.index)
select(query, [r, a, t], %{
artist_id: r.id,
name: r.name,
album_id: a.id,
album_title: a.title,
track_id: t.id,
title: t.title
})
end
def query_expression_composed() do
# composing a query expressions also requires a binding list
query = from(Artist)
query = from([r] in query, left_join: a in Album, on: r.id == a.artist_id)
query = from([r, a] in query, left_join: t in Track, on: a.id == t.album_id)
query = from([r, a, t] in query, order_by: [asc: r.id, asc: a.id, asc: t.index])
from([r, a, t] in query,
select: %{
artist_id: r.id,
name: r.name,
album_id: a.id,
album_title: a.title,
track_id: t.id,
title: t.title
}
)
end
def query_kw_named() do
# keyword query with named bindings
# when using named bindings
# the order or number of elements in the
# binding list no longer matters
# [x] "x" is "Artist" (positional binding)
# [artist: y] "y" is "Artist" (named binding)
#
query = from(r in Artist, as: :artist)
# [x,y] "x" is "Artist", "y" is "Album" (positional binding)
# [album: x, artist: y] "x" is "Album", "y" is "Artist" (named binding)
#
query = join(query, :left, [artist: r], a in Album, as: :album, on: r.id == a.artist_id)
# [x,y,z] "x" is "Artist", "y" is "Album", "z" is "Track" (positional binding)
# [track: x, album: y, artist: z] "x" is "Track", "y" is "Album", "z" is "Artist" (named binding)
#
query =
join(query, :left, [album: a, artist: r], t in Track, as: :track, on: a.id == t.album_id)
query = order_by(query, [track: t, album: a, artist: r], asc: r.id, asc: a.id, asc: t.index)
# Note that here we only need to list the bindings that we need
# [_, _, t] "t" is "Track" (positional binding). We don't need "Artist" and "Track" so there is an "_" for each.
# [track: x] "x" is "Track" (named binding). We don't need to list any binding we don't need.
#
select(query, [track: t], %{
id: t.id,
index: t.index,
title: t.title
})
end
def query_exp_named() do
# compositional query expression
# with named bindings
query = from(r in Artist, as: :artist)
query = from([artist: r] in query, left_join: a in Album, as: :album, on: r.id == a.artist_id)
query =
from([album: a, artist: r] in query,
left_join: t in Track,
as: :track,
on: a.id == t.album_id
)
query =
from([track: t, album: a, artist: r] in query,
order_by: [asc: r.id, asc: a.id, asc: t.index]
)
from([track: t] in query,
select: %{
id: t.id,
index: t.index,
title: t.title
}
)
end
