CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5will-paginate
Marcin Doliwa
Marcin Doliwa

Reputation: 3659

Most recent prices and will_paginate

I have two models Product and Price. Product can have multiple prices, each with different created_at, so at any time I can have product's price history.

I want to display list of products with most recent price next to each product, and use will_paginate to paginate 10 products per page.

I wrote something like this in my ProductsController:

@products = Product.includes(:prices).references(:prices).
  select('DISTINCT ON (products.id) *').
  order('products.id, prices.created_at ASC').
  paginate(page: params[:page])

When I check .to_sql for this query, I see that will_paginate adds WHERE \"products\".\"id\" IN (2, 2, 3, 3, 4, 4, 5, 5, 7, 7), and instead of 10 products I get 5.

When I try:

Product.includes(:prices).references(:prices).
      select('DISTINCT ON (products.id) *').
      order('products.id, prices.created_at ASC').limit(10).pluck(:id)

I get => [2, 2, 3, 3, 4, 4, 5, 5, 7, 7]

Any idea how can I change it to get 10 products with access to most recent price?

Upvotes: 1

Views: 36

Answers (1)

Marcin Doliwa
Marcin Doliwa

Reputation: 3659

I think I managed to do this with this:

@products = Product.
  select('DISTINCT ON(products.id) products.*, prices.value as recent_price').
  joins('LEFT OUTER JOIN prices ON products.id = prices.product_id').
  order('products.id ASC, prices.created_at DESC').
  paginate(page: params[:page])

Upvotes: 0

Related Questions