Ecto fragment subquery with conditional where

user_1606323 0
Viewed 1217

I'm trying to use ecto perform a query with a subquery as one of the SELECTs. In SQL it looks like this (player has_many votes):

SELECT
    players.id AS player_id,
    (SELECT count(*) FROM votes WHERE votes.player_id = players.id) AS vote_count
FROM
    players

however, depending on the existence of a parameter I want the SELECT subquery to have an extra WHERE clause. E.g. 

SELECT
    players.id AS player_id,
    (SELECT count(*) FROM votes WHERE votes.player_id = players.id AND votes.type = 'motm') AS vote_count
FROM
    players

In ecto, I've come up with this:

vote_count_query =
  from(p in Player,
    select: %{
      player_id: p.id,
      vote_count:
        fragment(
          "SELECT count(*) FROM votes WHERE votes.player_id = ?",
          p.id
        )
    }
  )

Say there's a variable vote_type that may or may not be nil, how can I conditionally add a where clause to the inner select subquery? E.g.

fragment(
  "SELECT count(*) FROM votes WHERE votes.player_id = ? AND votes.type = ?",
  p.id,
  ^vote_type
)

(If there's a better way of getting the count of votes for all players then I'd be glad to hear it. With a join it seems players for whom no votes exist are not returned.)

ecto elixir sql
1 Answers

Although the comment by @dogbert looks like it might work, I managed to acheive this by replacing the SELECT subquery with a left join:

def vote_count(team_id, vote_type \\ nil) do
  vote_count_query =
    Player
    |> select([p, v], %{
      player_id: p.id,
      vote_count: count(v.id)
    })
    |> where([p, _], p.team_id == ^team_id)
    |> group_by([p, _], p.id)

  # depending on whether we are filtering by vote_type use the appropriate join
  # condition
  if(is_nil(vote_type),
    do: vote_count_query |> join(:left, [p], v in assoc(p, :votes)),
    else:
      vote_count_query
      |> join(:left, [p], v in Vote, on: p.id == v.player_id and v.type == ^vote_type)
  )
end

It seems the key is to use a left outer join with the vote type in the join condition rather than the default inner join. Otherwise players with 0 votes are not returned in the result.

user_16063230
Related