CODEWITHSUNDEEP
sqlruby-on-railsactiverecordmodels
lmojzis
lmojzis

Reputation: 561

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

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.

Upvotes: 0

Views: 155

Answers (3)

Arctodus
Arctodus

Reputation: 5847

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)

Upvotes: 0

Mandeep
Mandeep

Reputation: 9173

If you look at your error

ERROR: invalid input syntax for type timestamp: "last_sale_at"

It clearly states that there's a syntax error in your query. Active record is expecting it to be of date time as your sales.created_at is a timestamp and you are suppling it as a string or some other type which is not of datetime type. You need to pass in a date object to get your query working.

FIX

You need to make sure that your last_sale_at field is of datetime

Upvotes: 0

Mohanraj
Mohanraj

Reputation: 4200

You should pass date object instead of 'last_sale_at', it should be something like below,

Customer.includes(:sales).where('sales.created_at' => Time.now)

Upvotes: 1

Related Questions