0

I have a "Has many through" relationship:

class Sale < ApplicationRecord
    has_many :sales_status_histories
    has_many :users, through: :sales_status_histories
    has_many :status_sales, through: :sales_status_histories
end

class StatusSale < ApplicationRecord
    has_many :sales_status_histories
    has_many :users, through: :sales_status_histories
    has_many :sales, through: :sales_status_histories
end

class User < ApplicationRecord
    has_many :sales_status_histories
    has_many :sales, through: :sales_status_histories
    has_many :status_sales, through: :sales_status_histories
end

class SalesStatusHistory < ApplicationRecord
    belongs_to :sale
    belongs_to :status_sale
    belongs_to :user
end

I want to select all the sales that has last specific status in the same query

I need to populate a panel with this informations

I'm doing this: 

@status = StatusSale.where(id: [2,9,10])
@date = DateTime.current.beginning_of_month..DateTime.current.end_of_month

@sales_col_2 = Sale.distinct.joins(:sales_status_histories).where(sales_status_histories: {created_at: @date}).where(sales_status_histories: { status_sale_id: @status })

But this returns sales that had the status ids in any moment and not in the last

Eduardorph
  • 153
  • 10

1 Answers1

1

You could use a "select" at the end if the number of element is not too big.

@status = StatusSale.where(id: [2,9,10])
@date = DateTime.current.beginning_of_month.DateTime.current.end_of_month

@sales_col_2 = Sale.distinct.joins(:sales_status_histories).where(sales_status_histories: {created_at: @date}).where(sales_status_histories: { status_sale_id: @status }).select {|s| @status.include?(s.sales_status_histories.last.status_sale_id)}

Else, this post might help you : Rails query through association limited to most recent record?

-- EDIT --

I think you could first start by getting the ids of all last SalesStatusHistory grouped by sale_id, THEN filtering them by which have to right status, and from that list, get all your Sale.

Based on that post: How to get the latest record from each group in ActiveRecord?, I think it could looks like:

@status = StatusSale.where(id: [2,9,10])
@date = DateTime.current.beginning_of_month.DateTime.current.end_of_month

@sales_status_ids = SalesStatusHistory.select("DISTINCT ON(sale_id) *").order("sale_id, created_at DESC").where(status_sale_id: @status).ids
@sales_col_2 = Sale.distinct.joins(:sales_status_histories).where(sales_status_histories: {id: @sales_status_ids})'

You could always merge the last two line in one big request, but I see no advantages.

Note: Some corrections are probably needed since I could not test it from where I am.

Martin Rioux
  • 21
  • 4