Recently I was playing around with extracting and updating data in the DB and for fun challenged myself to try to implement a nice-looking function/macro to do
SQL CASE with Ecto.
Wanted to share with you the results and thought it might be a good point to start blogging.
Any feedback is appreciated =)
Spoiler alert, throughout the blog post we are putting up together a
sql_case/2 macro that could be used as:
from(m in "movies",
[when: "G", then: "General Audiences"],
[when: "R", then: "Restricted"],
[when: "PG", then: "Parental Guidance Suggested"],
[when: "NC-17", then: "Clearly Adult"],
Personally I favor terseness in my Ecto queries so I would prefer a syntax more like this:
"G" => "General Audiences",
"R" => "Restricted",
"PG" => "Parental Guidance"}, m.rating)
Also, FWIW I’ve yet to find a case when I’ve needed
case despite maintaining a fairly extensive reporting system. I’d be tempted to say it’s almost a code smell in Ecto. For example, this example looks like it’s formatting the data, logic I prefer to keep in views. The other place I’ve seen it is in totaling with conditions which, at least in PSQL, can be converted to
I am working on the library that will allow something like:
defmodule Foo do
defq rating(rating) do
case rating do
"G" -> "General Audiences",
"R" -> "Restricted",
"PG" -> "Parental Guidance"
def query do
from m in Foo,
But it staled a little since I do not have enough time to spare. But the above example should work more or less.
Thank you for taking a look!
I agree, probably SELECT+CASE is not the best example. The only place that kinda made me go the rabbit hole was “UPDATE + CASE” when I wanted to update records with the value that’s set based on the condition. (similar to the last shown example in the article)
One thing I would add support is for is a key for
else. An additional benefit of this method would be the ability to dynamically build case statements, similar to
I’m using fragments for case statements currently, which is a bit painful.
I’ve been following your lib and patiently awaiting its official release—I need some
defq in my life! I don’t have a heck of a lot of time either but are you accepting PRs or prefer to solo it for now?
I am always accepting PRs.
Making well-functioning macros is hard, so I would very much like to see a PostgreSQL macro collection.
It would be great to be able to use functions (I especially use jsonb_* functions) without having to make macros for them myself. It’s error prone.