如何通过client_id选择max(日期)和group ?

So, I got this working the way I want in pure sql:

所以，我用我想要的方式在纯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

Here what I tried in the console:

这里是我在控制台尝试的:

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

It promptly exploded in my face with:

它立刻在我脸上爆炸:

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

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

It looks like I'm just getting the date itself returned from the select statement. I need something like "where effective_on = .select('max..."

看起来我只是得到了select语句返回的日期本身。我需要一些类似的东西，比如:“where effect _on = .select(‘max…”)。

any help would be greatly appreciated.

如有任何帮助，我们将不胜感激。

UPDATE: I'm getting closer with this:

更新:我正在接近这个:

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

but I'm not sure how to join the clients table in to get all the info I need. In the rails console, both of these:

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

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

cause this error:

造成这个错误:

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

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

UPDATE:

更新:

this is closer, but I need a having clause on the end - just not sure how to tie the inner table and outer table together (like in the sql: p.client_id = client_id):

这更接近了，但是我需要在最后有一个包含子句——只是不确定如何将内部表和外部表连接在一起(比如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))    

UPDATE: here's some progress. This seems to be what I need, but I don't know how to join it to the clients table:

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

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

This gives me the insurance_providers grouped by the client_id. I need to join to this resultset on the client_id.

这为我提供了由client_id分组的insurance_provider。我需要在client_id上加入这个resultset。

This does NOT work:

这并不工作:

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

resulting in a "NoMethodError":

导致“NoMethodError”:

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

UPDATE:

更新:

This is getting me the clients I need!

这是我需要的客户!

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.joins(insurance_providers)。(“insurance_providers。通过具有p的client_id从insurance_provider p组中选择MAX(effective ve_on)。client_id = insurance_providers.client_id)”)

But I can't get to the insurance_providers table. UGH! This is getting stickier....

但是我无法访问insurance_providers表。啊!这是得到粘性....

UPDATE:

更新:

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

sometimes taking a break is all you need.

有时候你只需要休息一下。

2 个解决方案

#1

I'm not sure why you were getting the undefined method 'group' error with the select in your first query, it shouldn't return anything at that point until you attempt to use one of the fields explicitly, or call .load or .first, etc.

我不确定为什么在第一个查询中使用select会得到未定义的方法'group'错误，在您尝试显式使用其中一个字段或调用.load或.first等之前，它不应该返回任何内容。

Maximum is not the way to go either because that WILL immediately return the data, and its basically the same as saying in SQL "SELECT MAX(effective_on) FROM insurance_providers", which is obviously not what you want.

最大值也不是一种方法，因为它会立即返回数据，这与在SQL中“从insurance_provider中选择MAX(effective ve_on)”是一样的，这显然不是您想要的。

To answer your question directly, to summarize what you're looking to do, I believe you're trying to find the Insurance Provider with the hightest effective_on date, and then be able to view the client associated with that provider. Please correct me if I am mistaken.

为了直接回答您的问题，总结您要做的事情，我相信您正在尝试找到具有最高有效值的保险提供者，然后能够查看与该提供者相关的客户端。如果我弄错了，请纠正我。

I think this will work:

我认为这是可行的:

insuranceProvider = 
    InsuranceProvider.where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers)")
                     .group(:client_id)

You shouldn't need anything else beyond that. Notice how I'm invoking the query on the InsuranceProvider model instead; Remember that with Rails, you can easily get the model object's associated records using your has_many and belongs_to relationship descriptors.

除此之外，你不需要其他任何东西。注意我是如何调用InsuranceProvider模型上的查询的;记住，使用Rails，您可以使用has_many和belongs_to关系描述符轻松地获取模型对象的关联记录。

Therefore, in order to get the associated Client model information, it is important that your InsuranceProvider class has a line that looks like belongs_to :client. This is required in this case, otherwise Rails doesn't know that this model relates to anything else. If you have that in there, then in order to get the Client information, all you simply need to do is

因此，为了获得相关的客户端模型信息，您的InsuranceProvider类必须有一个看起来像belongs_to: Client的行。在这种情况下，这是必需的，否则Rails不知道这个模型与其他任何东西相关。如果您有它，那么为了获得客户信息，您只需做的就是

client = insuranceProvider.client

客户= insuranceProvider.client

This will result in a second query which lazy-loads the client information for that insurance provider, and you're good to go.

这将导致第二个查询，该查询延迟加载该保险提供者的客户端信息，您可以继续。

EDIT

编辑

Based on the discussion in the comments, my original solution is not quite what you're looking for (and syntactically invalid for non-MySQL databases).

根据评论中的讨论，我最初的解决方案并不是您想要的(对于非mysql数据库，语法上是无效的)。

I answered a similar question here once that is somewhat related to this, so maybe that information could be helpful.

我在这里回答了一个类似的问题和这个有关，所以这个信息可能会有帮助。

Basically, what I think you'll need to do is grab your list of Clients, or grab your single client object, whatever you need to do, and invoke the association with a .where clause, like so:

基本上，我认为您需要做的是获取客户列表，或者获取您的单个客户对象，无论您需要做什么，并使用.where子句调用关联，如下所示:

client = Client.first # this is for illustration so that we have a client object to work with
insurance_provider = 
    client.insurance_providers
          .where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers WHERE client_id = ?", client.id)

Or, if you want to avoid having to inject the client.id manually, you can cause a dependent subquery, like so:

或者，如果您想避免注入客户端。您可以手动创建一个从属子查询，如下所示:

insurance_provider = 
    client.insurance_providers
          .joins(:client)
          .where("effective_on = (SELECT MAX(effective_on) FROM insurance_providers WHERE client_id = clients.id")

#2

So, in my controller, I have this:

在我的控制器中，我有

@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
                        )")

Then in my view, I have this:

在我看来，我有:

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