How to select max(date) and group by client_id?

Question

So, I got this working the way I want in pure 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

It looks like I'm just getting the date itself returned from the select statement. I need something like "where effective_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:

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

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):

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.

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":

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

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

UPDATE:

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

sometimes taking a break is all you need.

Answer 1

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

Answer 2

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.

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.

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.

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

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).

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:

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