Ruby on Rails: How to query relation objects based on date range

Question

I have

class Campaign < ApplicationRecord
 has_many :clicks
 has_many :cpas
 has_many :ftds
 has_many :signups
end

and

class Click < ApplicationRecord
  belongs_to :campaign
end

class Cpa < ApplicationRecord
  belongs_to :campaign
end

class Ftd < ApplicationRecord
  belongs_to :campaign
end

class Signup < ApplicationRecord
  belongs_to :campaign
end

The models clicks, cpas, ftds, signups are kind of events that happens throughout the time to the campaign model. I want to create a reports page the list all the campaign and show on each line the events (clicks, cpas, ftds, signups) that happened(created_at) on given time(on this month).

On my attemps I get the campaign with events on the month, but the data listed on the lines brings events that happened on other month I chose, but also others months.

def self.filter_by_date(start_date = Time.now.beginning_of_month, end_date = Time.now.end_of_month)
  joins(:clicks).where(clicks: {created_at: start_date..end_date}) +
  joins(:ftds).where(ftds: {created_at: start_date..end_date}) +
  joins(:cpas).where(cpas: {created_at: start_date..end_date}) +
  joins(:signups).where(signups: {created_at: start_date..end_date})
end

Sorry for the not so good english, I'm Brazilian.

Not sure which bit you are stuck on, this help? http://stackoverflow.com/questions/2381718/rails-activerecord-date-between — Tony Hopkinson, Mar 12 '17 at 23:04
My big problem is more related with the *joins* @TonyHopkinson. But thanks anyway :D — André Luiz, Mar 12 '17 at 23:38
Maybe you could add your query code showing an example of what doesn't work? — Marc Rohloff, Mar 13 '17 at 00:18
@MarcRohloff I edited the question, added more info and the code I tried, maybe it helps you help me :D — André Luiz, Mar 13 '17 at 00:43

score 1 · Accepted Answer · answered Mar 13 '17 at 03:40

Tenho certeza que sua Ingles e melhor do que o meu Portugues. It might help if you showed how you display this data. As you've written it it will get all Campaigns that have clicks, ftds, cpas or signups within the period and then (I assume) display all the clicks, etc for each Campaign. It might also have duplicate campaigns. Depending on what you are trying to do you could:

Get each type separately an display it:

clicks = Clicks.where(created_at: start_date..end_date)
render_clicks(clicks)
ftds = Ftds.where(created_at: start_date..end_date)
render_ftds(ftds)

Write something like:

def self.filter_by_date(start_date = Time.now.beginning_of_month, end_date = Time.now.end_of_month)
  condition = {created_at: start_date..end_date}  
  self.includes(:clicks ).where(clicks:  condition)
      .includes(:ftds   ).where(ftds:    condition)
      .includes(:cpas   ).where(cpas:    condition)    
      .includes(:signups).where(signups: condition)
end

This would give you all papers but only the clicks, ftds, cpas and signups that were created in between those dates.

If your goal is to return campaigns with activity between that period then it is possible to write a query to do it, but it would be complex and perform badly. I would add a last_activity' column to thecampaignstable of typedatetime`, then change your code as follows:

class Click < ApplicationRecord
  belongs_to :campaign, touch: :last_activity
end

class Cpa < ApplicationRecord
  belongs_to :campaign, touch: :last_activity
end

class Ftd < ApplicationRecord
  belongs_to :campaign, touch: :last_activity
end

class Signup < ApplicationRecord
  belongs_to :campaign, touch: :last_activity
end

def self.filter_by_date(start_date = Time.now.beginning_of_month, end_date = Time.now.end_of_month)
  where(last_activity: start_date..end_date)
end

Ruby on Rails: How to query relation objects based on date range

1 Answers1