A PostgreSQL/Activerecord question!!
I’m trying to get all contacts for whom their most recent email has a direction of “incoming”. It seems simple but I can’t seem to figure it out yet! Any help would be appreciated.
Contacts have many Emails, Emails belong to a Contact. Emails have an attribute (direction) that say whether they are “incoming” or “outgoing” messages.
So far the closest I’ve gotten is something like this…
Contact.joins(emails).select( max(emails.created_at) as newest_email )
But i have no way of knowing whether that newest_email’s direction.
I need something that’s basically like this (i know this is not correct code, but this is where my thinking is at):
Contact.joins(emails).select( max(emails.created_at) as newest_email).where(newest_email.direction == “incoming”)
So this is a tricky SQL problem in general. Unfortunately, there’s not really a good way to do that query without resorting to subqueries or other extremely slow and messy solutions. If possible, this is a great place to consider denormalization. Have you considered adding a newest_email_id column to Contact, with the following code?
class Contact
belongs_to :newest_email, class_name: 'Email'
end
class Email
after_create :set_newest_email_on_contact
private
def set_newest_email_on_contact
contact.update_attributes(newest_email: self)
end
end
That code assumes that an email knows its contact when it’s created, and that its contact never changes. If those statements aren’t the case, you could also do something a bit more complex.
class Email
after_save :set_newest_email_on_contact
private
def set_newest_email_on_contact
unless contact.emails.any? { |email| email.created_at > created_at }
contact.update_attributes(newest_email: self)
end
end
end
With the data denormalized, your query looks like this:
This is one hell of an answer! Thanks so much @seangriffin. Its great to know I wasn’t just banging my head for no reason on this. Really appreciate it!!
