don_Bigote don_Bigote - 5 days ago 5
Ruby Question

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?

Answer

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)
Comments