cjbottaro
Ecto.Query.API.fragment and string interpolation
I know this has been asked before, but I still can’t get it to work. I have code like this…
def case_stmt(input) do
[c1, c2, c3, c4] = generate_clauses(input)
Ecto.Query.dynamic([foo: a, bar: b], fragment("""
CASE WHEN ? THEN 1 ELSE 0 END +
CASE WHEN ? THEN 1 ELSE 0 END +
CASE WHEN ? THEN 1 ELSE 0 END +
CASE WHEN ? THEN 1 ELSE 0 END
""", ^c1, ^c2, ^c3, ^c4))
end
Which works fine when the number of generated clauses is exactly 4. What about when it’s an unknown amount? I want to build up the SQL string using reduce.
There is a really good blog post about making SQL case statements with Ecto:
The problem is that it doesn’t work when you pass it a variable; it only works when you pass a literal.
Any ideas on how to make this work? My use case is user defined queries that are stored in a database. All the user input is validated before making the db records of course. “Just make a case clause for each field the user can search on.” doesn’t work because the schema itself is user defined.
Thanks for the help!
Marked As Solved
cjbottaro
Figured it out after chasing down those ideas, but ultimately went a different route (I think).
clauses = generate_clauses(input)
clause_count = Enum.reduce(clauses, nil, fn clause, acc ->
q = Ecto.Query.dynamic(
[foo: a, bar: b],
fragment("CASE WHEN ? THEN 1 ELSE 0 END", ^clause)
)
if acc do
Ecto.Query.dynamic([foo: a, bar: b], ^q + ^acc)
else
q
end
end)
Ecto.Query.from(..., select_merge: ^%{clause_count: clause_count})
I need it done in SQL because I’m going to use that count in a where clause.
Very cool! I had been knee deep in Ecto docs for the past couple of days and still managed to miss that. Going to have to tuck that away in my head for later use. I tried using it here with a like a fragment("sum(?)", splice(clauses)) but it didn’t work cuz that’s not how sql sum works… 
Also Liked
dimitarvp
Why not separate them and do the summing in Elixir? You can also just use select_merge with dummy map keys and then use the resulting map’s values and sum that, similarly to this thread: Aggregate multiple dynamically selected columns in Ecto (without group by) - #3 by zackmichener
def case_stmt(input) do
clauses = generate_clauses(input)
bindings = [foo: a, bar: b]
Enum.reduce(clauses, false, fn clause, query ->
Ecto.Query.dynamic(
bindings,
fragment("CASE WHEN ? THEN 1 ELSE 0 END", ^clause) or ^query
)
end)
end
LostKobrakai
https://hexdocs.pm/ecto/Ecto.Query.API.html#fragment/1-splicing
This might help with variable number of elements, where you don‘t know the number of elements at compile time. Not sure if it works with CASE though.
dimitarvp
Oh that’s pretty clever. Nice job!
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Catch errors, track performance, monitor hosts and more.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Courses that'll move you from confusion to "Aha, now I get it!"