How do I best run a search on Date when it is not a :has_many association?

Question

I have a number of activities that have a calculated scheduled date. The activities, for example, Email, have a email.days method which is the days from a Contact.start_date on which it should be sent.

This means contact.start_date + email.days yields a date on which email is sent to contact.

I would like to use link_to around the date, so I can see all the emails and associated contacts that are to be scheduled on that date.

However, this "date" is not an attribute or an associate, so I'm not linking to a model's view. It's calculated.

So:

1) What should the actual "format" of the date that gets passed in the URl be? What is the method to do the consistent conversion?

2) How do I (find) all instances, because this "date" is not an actual attribute, is it a calculated value which changes depending on the two associated models of Contact and Email.

Thanks.

EDIT BELOW:

The resulting output would look like this:

John Johnes "Email subject number1"

Davey Jones "Email subject number 2"

Franklin Rooseveltn "Email subject number 1"

ETC...

Answer 1

Depending on whether these attributes are stored in a global config or define, or whether they're stored in the database you may be able to do the math in your SQL statement. This is the fastest way.

Otherwise you can do something like this:

Email.find(:all).reject { |email| email.contact.start_date + email.days <= Date.now }

Obviously, I'm missing some details about your data model but hopefully this gives you an idea about how to filter easily.

Answer 2

You can add a index method to your EmailsController:

class EmailsController < ApplicationController
  def index

    @contact_emails = ContactEmail.all(:include => [:contact, :email], 
     :conditions => ["contacts.created_at <= DATE_SUB(?, INTERVAL 
                             emails.days DAY)", 
     params[:due_date])
    # now you can access the email as
    # contact_email.email
    # contact_email.contact

  end
end

Link to with the due date:

link_to("Due today", emails_path(:due_date => Date.today))

Edit 1

To get the list emails due today:

  def index
    @emails = Emails.all(:conditions => [ " EXISTS ( 
                                   SELECT * 
                                   FROM   contacts A 
                                   WHERE  A.start_date = DATE_SUB(?, 
                                                INTERVAL emails.days DAY)
                                  )", params[:due_date].to_date)    
  end

The query above expects the start_date to be a Date field.

Edit 2

Query for returning the data from contact and emails:

  def index
    joins = Contact.send(:sanitize_sql_array, 
               ["JOIN emails AS emails ON contacts.start_date = 
                    DATE_SUB(?, INTERVAL emails.days DAY)",
                params[:due_date].to_date 
               ])

    @contacts = Contact.all(:joins => joins, 
                :select => "contacts.*, emails.subject AS email_subject") 
  end

Now you can iterate through contacts

@contacts.each do |contact|
  contact.first_name #John
  contact.last_name #Clive
  contact.email_subject #Subject 1
end

If you need more fields from Emails add them to the :select clause(similar to subject).

Edit 3

I have further optimized the solution:

  def index
    joins = Contact.send(

    @contacts = Contact.all(:joins => "JOIN emails AS emails", 
                  :select => "contacts.*, emails.subject AS email_subject",
                  :conditions => ["contacts.start_date = 
                                   DATE_SUB(?, INTERVAL emails.days DAY)",
                                   params[:due_date].to_date 
                               ]
               )

  end