CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
don_Bigote
don_Bigote

Reputation: 947

How can I write the following query in Rails?

I need to write a query that only counts individuals that have not attended an event previously.

Here are the models

class Individual < ActiveRecord::Base
  has_many :attendances, dependent: :destroy
  has_many :events, through: :attendances
end

class Attendance < ActiveRecord::Base
  belongs_to :event
  belongs_to :individual
end 

class Event < ActiveRecord::Base
  has_many :attendances, dependent: :destroy
  has_many :individuals, through: :attendances
end

In my view I have 2 different queries. Each query measures event attendance during each fiscal year.

Query for FY 2015:

  <%= Event.published.joins( :attendances => :individual ).where( 
  :events => {:fiscal_session => "2014-10-01".."2015-09-30"}
  ).distinct.count(:individual_id) %>

Query for FY 2016:

  <%= Event.published.joins( :attendances => :individual ).where( 
  :events => { :fiscal_session => "2015-10-01".."2016-09-30"}
  ).distinct.count(:individual_id) %>

The problem is that the query for FY 2016 does not ignore individuals that attended an event in FY 2015.

I am using postgresql, and I have tried to write scopes and helper methods that would filter out individuals that attended in FY 2015. But I just haven't been able to figure it out.

For example, here is FY 2015:

<%= Individual.joins(attendances: :event).where( 
:events => {:fiscal_session => "2014-10-01".."2015-09-30"}
).group('individuals.id').having('count(attendances.id) > 0').count %>

The result is:

{2787=>1, 3469=>1}

And this is for FY 2016

<%= Individual.joins(attendances: :event).where( 
:events => {:fiscal_session => "2015-10-01".."2016-09-30"}
).group('individuals.id').having('count(attendances.id) > 0').count %>

The result is this:

{2905=>1, 3444=>1, 2787=>1, 2790=>1, 2858=>1}

As you can see, individual_id 2787 is counted twice. I do not want to count 2787 in the query for FY 2016.

What is the proper way to achieve the desired count with Rails and ActiveRecord?

Upvotes: 1

Views: 63

Answers (3)

user2536065
user2536065

Reputation:

I think the best way for you to accomplish what you want to do is with two queries.

ignore_individuals = Event
.published
.joins(attendances: :individual)
.where( 
  'events.fiscal_session <= ?', '2015-09-30'
)
.pluck(:individual_id)

Event
.published
.joins(attendances: :individual)
.where.not(individual_id: ignore_individuals)
.where( 
  events: { fiscal_session: "2015-10-01".."2016-09-30" }
).distinct.count(:individual_id)

Upvotes: 2

SomeSchmo
SomeSchmo

Reputation: 665

Not positive but if you're looking for a count of individuals who've attended an event in 2016 and your tables have timestamps you I believe you can do

Individual.joins(:attendances).where("attendances.created_at > ?", "01-01-2016").count

Upvotes: 1

kriskova
kriskova

Reputation: 578

I think you are looking for: Individual.includes(:events).where( events: { id: nil } ).count

These are the people who never attended any events.

Upvotes: 1

Related Questions