CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
user984621
user984621

Reputation: 48521

Rails + PostgreSQL: How to get products that have been ordered at least once + get the order # and the manager id

I have these models:

class Product < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
 belongs_to :product
end

I want to get a list of all products which have been ordered at least once along with the timestamp of when the order has been made (orders.created_at) and the code of the order (orders.no) and the admin who processed the orderd (orders.processed_by_admin_id). And finally, I would want to list the output by orders.created_at.

After some googling, I was able to come up with something like this:

Product.joins(:orders)
       .group("(products.id) having count(products.id) > 0")
       .select("products.*", "max(orders.created_at) as last_order_time")
       .sort_by(&:last_order_time).reverse

This gets me the list of products ordered at least once, sorted out by the timestamp. However, I got stucked here being unable to fetch yet the orders.no and orders.processed_by_admin_id. I am not sure if the procedure I follow is the right one.

The desired output is something like:

products.* | last_order_time | order_no | order_processed_by_admin_id

Any advice appreciated.

Upvotes: 2

Views: 193

Answers (1)

Jared Beck
Jared Beck

Reputation: 17538

I want to get a list of all products which have been ordered at least once along with the timestamp of when the order has been made (orders.created_at) and the code of the order (orders.no) and the admin who processed the orderd (orders.processed_by_admin_id). And finally, I would want to list the output by orders.created_at.

When writing a complicated query, I like to write the plain SQL first.

Let's start with a query to get the most recent order for each product.

select product_id, id, 
  row_number() over ( 
    partition by product_id order by created_at 
  ) as recency_ranking 
from orders
;

This is called a window function. For each product, the most recent order will have a recency_ranking of 1. Now we can join this query to our products table.

select p.*,
  x.id as order_id,
  x.no as order_number,
  x.order_processed_by_admin_id,
  x.created_at as order_created_at
from products p
inner join (
  select product_id, 
    id, 
    created_at, 
    row_number() over ( 
      partition by product_id order by created_at 
    ) as recency_ranking,
    no,
    order_processed_by_admin_id 
  from orders
) x
  on x.product_id = p.id
    and x.recency_ranking = 1
order by x.created_at
;

When we join another query like this it's called a subquery. Note how we join on recency_ranking = 1.

To run this whole query in ActiveRecord, I'd suggest find_by_sql:

products = Product.find_by_sql('select p.*, x.id ...')
products.first.order_created_at #=> '2019-01-01 ...'

Upvotes: 2

Related Questions