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.