CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
mdegis
mdegis

Reputation: 2448

Double join on same table produces wrong result

Basically I have a Driver model that has many rides. Those rides has price field and I want to calculate driver's total_paid (the payment they have earned for all the time) and this_week_paid (the payment has been done only from the beginning of this week to the end of it) in one active record query.

I have achieved the correct number for total_paid part easily with one join like this:

Driver.joins(:rides).
  select("#{Driver.table_name}.*, sum(substring(rides.price from '[0-9]+.[0-9]*')::numeric) as total_paid").
  group("#{Driver.table_name}.id").
  order("total_paid DESC, id")

Now when I try to add this_week_paid to that query:

Driver.joins("INNER JOIN rides this_week_rides ON #{Driver.table_name}.id = this_week_rides.driver_id").
  joins("INNER JOIN rides all_rides ON #{Driver.table_name}.id = all_rides.driver_id").
  select("#{Driver.table_name}.*, " +
             "sum(substring(this_week_rides.price from '[0-9]+.[0-9]*')::numeric) as this_week_paid, " +
             "sum(substring(all_rides.price from '[0-9]+.[0-9]*')::numeric) as total_paid").
  where(this_week_rides: { created_at: Time.current.beginning_of_week..Time.current.end_of_week }).
  group("#{Driver.table_name}.id").
  order("this_week_paid DESC, id")

It runs without throwing any exceptions however, interestingly the total_paid field is two times of correct number and this_week_paid field is three times of the correct one ( Query answer: { this_week_paid: 188.46, total_paid: 159.9 }, the correct answer: { this_week_paid: 62.82, total_paid: 79.95 } ).

I did try to add where("this_week_rides.id != all_rides.id") and it gives me another wrong result ("this_week_paid" => 125.64,"total_paid" => 97.08)

What am I missing?

Upvotes: 2

Views: 65

Answers (1)

Björn Nilsson
Björn Nilsson

Reputation: 3773

You join the same table twice and that will multiply the number of rows you get so that is why you get multiples of the expected result. Just join it once and filter in the select like this:

sum(substring(rides.price from '[0-9]+.[0-9]*')::numeric) filter (
  where rides.created_at between time1 and time2
) as this_week_paid,

sum(substring(rides.price from '[0-9]+.[0-9]*')::numeric) as total_paid

Upvotes: 2

Related Questions