代码之家 › 专栏 › 技术社区 › harryg

带条件where的exto片段子查询

ecto elixir sql

harryg · 技术社区 · 4 年前

我正在尝试使用exto执行一个查询,其中子查询是 SELECT 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

但是,根据参数的存在,我希望 选择 子查询有一个额外的 WHERE

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

在外太空,我想到了这个:

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
        )
    }
  )

假设有一个变量 vote_type 可能是也可能不是 nil ,如何有条件地添加 where 子句到内部select子查询?例如。

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

(如果有更好的方法来计算 全部的

0 回复 | 直到 4 年前

harryg 4 年前

尽管 the comment 在@dogbert看来它可能有用,我通过用左连接替换SELECT子查询来实现这一点:

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

看来关键是使用左边在联接条件中使用投票类型的外部联接,而不是默认的内部联接。否则结果中不会返回0票的玩家。