Rails eager load where field matches the parent's field?

Question

I'm trying to eager load a model where I try to eager load a single record.

Let's say:

Customer.includes(:sales).where('sales.created_at' => 'customer.last_sale_at')

but instead of working SQL WHERE:

... WHERE "sales"."created_at" = "customer"."last_sale_at"

 count 
-------
  1990
(1 row)

rails produces invalid:

... WHERE "sales"."created_at" = 'customer.last_sale_at'

ERROR:  invalid input syntax for type timestamp: "last_sale_at"
LINE 1: ...d" = "customers"."id" WHERE "sales"."created_at" = 'last_sale...

I also tried:

Customer.includes(:sales).where('sales.created_at' => '"customer"."last_sale_at"')
Customer.includes(:sales).where('sales.created_at' => :last_sale_at)
Customer.includes(:sales).where('"sales"."created_at" = "customer"."last_sale_at"')

which produced variety of errors.

What's wrong?

EDIT: I updated the question to be more self-explanatory.

Answer 1

You can't call conditions when using "includes" because the records are being eager loaded in a separate query. You need to use a join instead:

Customer.joins(:sales).where('sales.created_at = customers.last_sale_at')

That being said, I suggest you use a slightly different architecture where you you create an actual relation on customer that points to the last sale, I.E a foreign_key 'last_sale_id'.

class Customer < ActiveRecord::Base
  has_many :sales
  belongs_to :last_sale, class_name: 'Sale'
end

When a sale is created you can update the customers last_sale in a callback:

class Sale < ActiveRecord::Base
  after_create :update_customers_last_sale

  private

  def update_customers_last_sale
    customer.last_sale = self
    customer.save!
  end
end

With that structure you can do this to load all customers with their last sale:

Customer.includes(:last_sale)