Improve on these ActiveRecord requests?

I am trying to get the top 5 products, based on the impressions that product has recieved.

Now the code below is working ok, but its not in the right order, and I cant work out a better way to get the impressions.

@products.joins("INNER JOIN products ON products.id = I.product_id")
.from(Impression.select("product_id, COUNT(product_id) as visits")
.group("product_id").limit(5).order("visits DESC"),:I)

Also, while i am here, is there also a more effiecient way to write out the getting of the total impressions for the last 30 days, based on a selection of products? So far i have the following...

Once again, it works ok, but it feels a little cumbersome. Maybe there is a i could combine both?

@impressions = Impression.joins(:product)
.where('impressions.created_at > ?', 30.days.ago)
.where(products: { id: @products.pluck(:id) })

You might try adding your own caches to the Product table. You could have a nightly or hourly cron update the cache so it isn't constantly being changed, but still getting regular updates.

Do you mean have a column in the Products table which houses the count figure, and have a job to do the calculation each night, and update that figure?

That would be fine, but I may need to get the impressions for the last 7 days, 30 days, 6 months or even a year. So storing it may not be the mode reusable solution.

I have 2 tables, Products and Impressions which are related, and impressions belong to products.

maybe i need to get all products, and include the impressions count? that way it should be more efficient.

Yeah, just adding a column like visits_last_30_days:integer and having it update every hour or night. You'd just query the impressions, sum up the total and save it to that column each time.

If you had a few common options you could cache multiple values so those common ones were super fast. Then fallback to doing the JOIN query for custom ranges which will be a little slower.

You should be able to query the Product table and join the impressions and use that for sorting. Here's an example of doing that: https://stackoverflow.com/questions/22234983/rails-activerecord-sort-by-count-of-join-table-associations

Skipping the cache will help you keep things simpler, since you'll always need the fallback join query.

I will give this a try, but i get the idea of having last 30 days as the default. It makes sense.

I could use JSONB to store the visits i suppose, and just update them every few hours.

Cheers Chris, given me something to think about this evening.