I recently struggled with converting the following SQL query for use with Elixir’s Ecto library:

SELECT topics.id,
  topics.slug,
  topics.name,
  (CASE WHEN likes.id > 0
    THEN true
    ELSE false
    END) AS user_does_like
FROM topics
LEFT OUTER JOIN likes
  ON topics.id = likes.topic_id
  AND likes.user_id = 1;

What this query does is grab all topics along with whether or not the given user has liked the topic.

The complication came from the fourth select field where there’s an alias involved:

(CASE WHEN likes.id > 0
 THEN true
 ELSE false
 END) AS user_does_like

How does one represent this in Ecto? After diving into the docs and code I came up with the following:

user_id = 1
Topic
  |> join(:left,
     [t],
     l in Like,
     (t.id == l.topic_id
      and l.user_id == ^user_id))
  |> select([t,l], %{
      id: t.id,
      slug: t.slug,
      name: t.name,
      user_does_like: fragment("(CASE WHEN l1.id > 0 THEN true ELSE false END) AS user_does_like")
    })
  |> Repo.all

The key is the use of the fragment function in a Map passed as the second argument to the select function.

The downside to this solution is that you need to explicitly declare the selected fields, rather than just taking all fields from the topic model and appending the user_does_like. This way the query would be immune to most changes in the data model. For example it would be nice if the following also worked and grabbed all fields in the topic model along with appending user_does_like:

Topic
  |> join(:left,
     [t],
     l in Like,
     (t.id == l.topic_id
      and l.user_id == ^user_id))
  |> select([t,l], [t, %{
      user_does_like: fragment("(CASE WHEN l1.id > 0 THEN true ELSE false END) AS user_does_like")
    }])
  |> Repo.all

I suspect there is a way to accomplish this. Please email me if you know.