CODEWITHSUNDEEP
ruby-on-railsactiverecordjoinhas-many
NotDan
NotDan

Reputation: 32233

Rails 3 - How do I join on a table and filter for last X days?

I have Clients that has_many Transactions. I want to select all Clients who haven't had any transactions in the last 30 days (Transaction has a transaction_date) and display the client's name and the last transaction date.

How can I do this query in rails?

Upvotes: 1

Views: 641

Answers (3)

Michael Durrant
Michael Durrant

Reputation: 96544

In your transaction model:

scope :trans_order => (:order => "transaction date ASC")

In your controller 

@inactive_clients = 
Client(:include => :transactions).where
("max(transaction_date) < ? AND transaction_date IS NOT NULL", 
Time.now - 30.days)

Then in your view:

@inactive_clients .each |client|
  = client.name
  = client.transactions.trans_order.last.transaction_date
end

Upvotes: 2

redronin
redronin

Reputation: 723

@client_transactions = Transaction.includes(:client).where('transaction_date < ?', 30.days.ago).group(:client_id)
@client_transactions.each do |client_transaction| 
  puts "client is #{client_transaction.client.name}"
end

Upvotes: 1

Rob Di Marco
Rob Di Marco

Reputation: 44962

I would use a not exists clause like

@clients = Client.where("not exists (select 'x' from 
    transactions where transactions.client_id = clients.id 
    and transaction_date > ?)", 
  30.days.ago)

Upvotes: 0

Related Questions