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