假设我试着计算一段时间内每个部门的事故数.所以我有两张桌子.我正在try 使用ActiveRecord获取我的答案,结果如下所示
class Division < ApplicationRecord
has_many :accidents
end
class Accident < ApplicationRecord
belongs_to :division
end
Division.left_joins(:accidents).where('accidents.occurred_at > ?', Time.now - 1.year).group(:name).count
在本例中,ActiveRecord生成该SQL
SELECT COUNT(accidents.id) AS "count_all", "divisions"."name" AS "divisions_name"
FROM "divisions"
LEFT OUTER JOIN "accidents" ON "accidents"."division_id" = "divisions"."id"
WHERE (accidents.occurred_at > '2022-07-30 20:56:10.178153')
GROUP BY "divisions"."name"
这里的问题是,如果某些部门的意外计数为0,我们将不会在查询结果中看到它,所以我需要SQL是这样的
SELECT COUNT(accidents.id) AS "count_all", "divisions"."name" AS "divisions_name"
FROM "divisions"
LEFT OUTER JOIN "accidents" ON "accidents"."division_id" = "divisions"."id" and accidents.occurred_at > '2022-07-30 20:56:10.178153'
GROUP BY "divisions"."name"
是否可以为JOIN指定一些附加条件? 我知道我们可以为HAS_MANY关系指定其他条件,但这将是静态条件.我希望它是动态的,具体取决于用户请求参数
我正在try 避免将原始SQL用于连接条件E.Q.
Division.joins("LEFT OUTER JOIN accidents ON accidents.division_id = divisions.id
and (accidents.occurred_at > '2022-07-30 20:56:10.178153'").group(:name).count('accidents.id')