Elixir Blog Post: SQL CASE with Ecto

[RudManusachi]

SQL CASE clause with Ecto

Hi there!

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",
  select:
    {m.title,
     sql_case(m.rating, [
       [when: "G", then: "General Audiences"],
       [when: "R", then: "Restricted"],
       [when: "PG", then: "Parental Guidance Suggested"],
       [when: "NC-17", then: "Clearly Adult"],
       [else: m.rating]
     ])}
)

[tfwright]

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.

[hauleth]

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.

[RudManusachi]

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)

[mpope]

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.

[sodapopcan]

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?

[fuelen]

Similar topic

Cond in Ecto query DSL

Hi all! Just want to share a small code snippet which allows writing CASE expressions using macro which is similar to cond. Here is an example of usage: import Ecto.Extension.Query.API import Ecto.Query from users in "users", select: %{ email: users.email, role_label: cond_ do users.role == "director" -> "DIRECTOR!" users.inserted_at < ago(6, "month") -> "OLD!" true -> type(users.role, :string) end } generated SQL: SELECT u0."email", CASE WHE…

[hauleth]

I am always accepting PRs.

[norpan]

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.