How apply additional conditions for ActiveRecord joins

Question

Suppose I try to calculate a count of accidents for each division for some period of time. So I have 2 tables. I am trying to use ActiveRecord to get my answer and it looks like this

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

In this case ActiveRecord generate this 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"

Problem here is that if accidents count for some division is 0 we will not see it in query result, so I need SQL to be like this

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"

Is it possible to specify some additional conditions for join? I know that we can specify additional conditions for has_many relation, but it will be a static conditions. I want it to be dynamic depending on user request parameters

I am trying to avoid using raw sql for join condition 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')

Answer 1

What's about using scope with JOIN to simplify final query?

class Division < ApplicationRecord
  scope :with_accidents_from, ->(occurred_at) do
    joins_query =
      sanitize_sql([
        'LEFT OUTER JOIN accidents ON accidents.division_id = divisions.id AND accidents.occurred_at > ?',
        occurred_at
      ])

    joins(joins_query)
  end
end

And then

Division.with_accidents_from(1.year.ago).group(:name).count('accidents.id')