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?

score 1 · Accepted Answer · edited May 23 '17 at 11:44

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)

edited May 23 '17 at 11:44

Community

1
1

answered Nov 26 '15 at 21:53

knut

27,320
6
84
112

This is exactly what I was looking for, didn't know about the _dataset thing. Perfect! – Florin Asăvoaie Nov 27 '15 at 11:57
I detected it last week ;) – knut Nov 27 '15 at 15:17

score 0 · Answer 2 · answered Nov 26 '15 at 18:21

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)

Sequel relations and SQL functions

2 Answers2