Rails - Create nil value when performing grouped functions on filtered out values

user_4833129 0
Viewed 82

Let's say I have a User table connected to a Payment table. I want to find the sum of all of a user's payments, even if they didn't make any. I might do something like:

User.left_joins(:payments).group(:id).pluck(:id, "SUM(amount)")

This would return nil values for the users who don't have a payment record. However, if I apply any kind of filtering, that aspect is lost. For example:

User.left_joins(:payments).where("payment_date > '2021-01-01'").group(:id).pluck(:id, "SUM(amount)")

The output will only contain users who made a payment after 2021-01-01. There will be no mention of users who only made payments before 2021 or never made a payment at all. Is there a way to maintain this information as nil or 0, similar to how it was done in the first query?

postgresql ruby-on-rails sql
1 Answers

You are correct an explicit condition on an OUTER JOIN creates an INNER JOIN. In order for this to perform as requested you would need a complex join condition like

join_condition = Arel::Nodes::OuterJoin.new(
    Payment.arel_table,
    Arel::Nodes::On.new(
      Payment.arel_attribute(:user_id).eq(User.arel_attribute(:id))
        .and(Payment.arel_attribute(:payment_date).gt('2021-01-01'))
    )
  )
User.joins(join_condition).group(:id).pluck(:id, "SUM(amount)")

This will result in something akin to

SELECT 
  users.id,
  SUM(amount) as exp1 
FROM 
  users
  LEFT OUTER JOIN payments ON payments.user_id = users.id
    AND payments.payment_date > '2021-01-01' 
GROUP BY 
  users.id
user_19782510
Related