ruby-on-rails - 如何选择 max(date) 和 group by client_id？

Question

所以，我在纯 sql 中按照我想要的方式工作:

select * from clients c 
    join insurance_providers p on c.id = p.client_id 
where p.effective_on = 
    (select max(effective_on) 
         from insurance_providers group by client_id having p.client_id = client_id)
and user_id = 2; #user_id =2 where 2 represents current_user.id

这是我在控制台中尝试过的:

Client.joins(:insurance_providers)
      .select('max(insurance_providers.effective_on)')
      .group(:client_id)
      .where('user_id = 2')

它立即在我的脸上爆炸:

NoMethodError: undefined method `group' for Mon, 08 Jul 2013:Date

看起来我只是从 select 语句返回日期本身。我需要类似 "where effective_on = .select('max..." 的东西

任何帮助将不胜感激。

更新:我越来越接近这个:

InsuranceProvider.maximum(:effective_on, :group => 'client_id')

但我不确定如何加入客户表以获取我需要的所有信息。
在 rails 控制台中，这两个:

Client.joins(:insurance_providers).maximum(:effective_on, :group => 'client_id')
Client.joins(:insurance_providers.maximum(:effective_on, :group => 'client_id'))

导致此错误:

NoMethodError: undefined method `group' for Mon, 08 Jul 2013:Date

更新:

这更接近，但我需要在最后有一个子句 - 只是不确定如何将内表和外表联系在一起(如在 sql 中:p.client_id = client_id):

insuranceProvider = InsuranceProvider.where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers group by client_id)")
  InsuranceProvider Load (1.0ms)  SELECT "insurance_providers".* FROM "insurance_providers" WHERE (effective_on = (SELECT MAX(effective_on) FROM insurance_providers group by client_id))
PG::CardinalityViolation: ERROR:  more than one row returned by a subquery used as an expression
: SELECT "insurance_providers".* FROM "insurance_providers"  WHERE (effective_on = (SELECT MAX(effective_on) FROM insurance_providers group by client_id))
ActiveRecord::StatementInvalid: PG::CardinalityViolation: ERROR:  more than one row returned by a subquery used as an expression
: SELECT "insurance_providers".* FROM "insurance_providers"  WHERE (effective_on = (SELECT MAX(effective_on) FROM insurance_providers group by client_id))    

更新:这里有一些进展。
这似乎是我需要的，但我不知道如何将它加入到客户端表中:

InsuranceProvider.where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers p group by client_id having p.client_id = insurance_providers.client_id)")

这给了我由 client_id 分组的 insurance_providers。我需要在 client_id 上加入这个结果集。

这不起作用:

InsuranceProvider.where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers p group by client_id having p.client_id = insurance_providers.client_id)").client

导致“NoMethodError”:

undefined method `client' for #<ActiveRecord::Relation::ActiveRecord_Relation_InsuranceProvider:0x007fe987725790>

更新:

这让我得到了我需要的客户!

Client.joins(:insurance_providers).where("insurance_providers.effective_on = (SELECT MAX(effective_on) FROM insurance_providers p group by client_id having p.client_id = insurance_providers.client_id)")

但是我无法访问 insurance_providers 表。啊!这越来越粘了......

更新:

client.insurance_providers.order('effective_on DESC').first.copay

有时您只需要休息一下。

Answer 1

所以，在我的 Controller 中，我有这个:

@clients = Client.joins(:insurance_providers)
                 .where("insurance_providers.effective_on = (
                           SELECT MAX(effective_on) 
                           FROM insurance_providers p 
                           GROUP BY client_id 
                           HAVING p.client_id = insurance_providers.client_id
                        )")

然后在我看来，我有这个:

client.insurance_providers.order('effective_on DESC').first.copay