Sequel relations and SQL functions

Question

I am using Ruby Sequel and I need to find the total amount spent by a customer since a specific date. This code is working:

customer = Customer.where(username: params[:username]).first
unless customer
  exit
end
Purchases.where(customer: customer).and('date < ?', params[:date]).sum(:amount)

However, I am wondering if there is a way to use the Model relation between Customer and Purchases and not use a where clause to find the Purchases, so that the code can look cleaner.

I was thinking about something like customer.Purchases.where(...).sum(...) but it doesn't work.

Any idea if there is a way to do this?

Answer 1

You already mentioned

was thinking about something like customer.Purchases.where(...).sum(...) but it doesn't work.

and in your comment in another answer you mention the relation with customer.purchases already exists.

Then you should also have a purchases_dataset-method. It is a Sequel::SQLite::Dataset and returns models.

So you could try:

customer.purchases_dataset.where('date < ?', params[:date]).sum(:amount)

(If it does not work: Can you please post your table and model definition to make a test)

Answer 2

It seems you aren't able to use associations declared in model in order to join a table.

Sequel documentation proposes only following way to generate right SQL:

Album.join(:artists, :id=>:artist_id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id

In you case it can look like this:

Customer.join(:purchases, customer_id: :id).where('date < ?', params[:date]).sum(:amount)