← Back to Upcase

Using an aliased column in a where clause?


(Austin Wang) #1

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

Let me know if I’m approaching this all wrong…


(Sean Griffin) #2

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:

Contact.joins(:newest_email).where(newest_email: { direction: :incoming })

(Austin Wang) #3

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!!


(Sean Griffin) #4

Any time. :smile: