CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
leemour
leemour

Reputation: 12003

RIGHT OUTER JOIN returns empty results with WHERE

I need to produce a report of all records (businesses) created by a particular user each month over last months. I produced the following query and expect it to provide me with a row for each month. However, this user didn't create any records (businesses) these months so I get an empty result [].

I'm still expecting to receive a row for each month, since I'm selecting a generate_series column using RIGHT OUTER JOIN but it doesn't happen. 

start = 3.months.ago
stop  = Time.now

new_businesses = Business.select(
    "generate_series, count(id) as new").
  joins("RIGHT OUTER JOIN ( SELECT
    generate_series(#{start.month}, #{stop.month})) series
    ON generate_series = date_part('month', created_at)
  ").
  where(created_at: start.beginning_of_month .. stop.end_of_month).
  where(author_id: creator.id).
  group("generate_series").
  order('generate_series ASC')

How can I change my query to get a row for each month instead of an empty result? I'm using PosgreSQL.

UPDATE

This code works:

new_businesses = Business.select(
    "generate_series as month, count(id) as new").
  joins("RIGHT OUTER JOIN ( SELECT
    generate_series(#{start.month}, #{stop.month})) series
    ON (generate_series = date_part('month', created_at)
      AND author_id = #{creator.id}
      AND created_at BETWEEN '#{start.beginning_of_month.to_formatted_s(:db)}' AND
        '#{stop.end_of_month.to_formatted_s(:db)}'
    )
  ").
  group("generate_series").
  order('generate_series ASC')

Upvotes: 1

Views: 1305

Answers (1)

Abelisto
Abelisto

Reputation: 15614

Your problem is in the where part which is breaks any outer joins. Consider the example:

select *
from a right outer join b on (a.id = b.id)

It will returns all rows from b and linked values from a, but:

select *
from a right outer join b on (a.id = b.id)
where a.some_field = 1

will drops all rows where a is not present.

The right way to do such sings is to place the filter into the join query part:

select *
from a right outer join b on (a.id = b.id and a.some_field = 1)

or use subquery:

select *
from (select * from a where a.some_field = 1) as a right outer join b on (a.id = b.id)

Upvotes: 2

Related Questions