Elixir Blog Post: SQL CASE with Ecto

Personally I favor terseness in my Ecto queries so I would prefer a syntax more like this:

case(m.rating, %{
  "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 filter.

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"
    end
  end

  def query do
    from m in Foo,
      select: rating(m.rating)
  end
end

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 select_merge.

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?

Similar topic

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.