Select highest ID from group

Question

I'm trying to group a table (oauth_access_tokens) by application_id and select the highest record by ID from the corresponding group (see the complete model below). I've seen this post, which explains how to get the highest ID from the group, but sadly it's not working for my case.

I have a table called oauth_access_tokens with the following attributes: id, resource_owner_id, application_id, token, refresh_token, expires_in, scopes and revoked_at.

The method I have in my model:

def last_token
  Doorkeeper::AccessToken.group(:application_id).having('id = MAX(id)')
end

After calling it like so: User.first.last_token (I made sure that there are a few records in the database present).

sql output:

Doorkeeper::AccessToken Load (0.5ms)  SELECT `oauth_access_tokens`.* FROM `oauth_access_tokens` GROUP BY `oauth_access_tokens`.`application_id` HAVING id = MAX(id)

Output: #<ActiveRecord::Relation []>

Why don't I get the record with the highest ID? When I run User.first.last_token I expect to see the access_token with the id of 28.

Happy holidays!

Wasif Hossain · Answer 1 · 2017-12-25T11:52:54.183

Given that id is the primary key of :users table and oauth_access_tokens.resource_owner_id points to users.id, something like this should work:

class User < ApplicationRecord
  ...
  def last_token
    # 'id' (or, self.id) in 'resource_owner_id: id' points to the user id, i.e. 'User.first.id' in 'User.first.last_token'
    Doorkeeper::AccessToken.group(:application_id).where(resource_owner_id: id).pluck('max(id)').first
  end
  ...
end

Though the above solution should work, but you can improve the query writing to a more readable way by defining the associations inside corresponding models like below:

class User < ApplicationRecord
  ...
  has_many :access_tokens,
    class_name: 'Doorkeeper::AccessToken',
    foreign_key: :resource_owner_id
  ...
  def last_token
    access_tokens.group(:application_id).pluck('max(id)').first
  end
  ...
end

class Doorkeeper::AccessToken < ApplicationRecord
  ...
  self.table_name = 'oauth_access_tokens'
  belongs_to :resource_owner, class_name: 'User'
  ...
end

In both cases, User.first.last_token will return 28 as you would expect.

Update

You are only 1 query away to get the access_token instances instead of mere ids. The method will now return an ActiveRecord::Relation of Doorkeeper::AccessToken instances which meet the defined criteria.

def last_tokens # pluralized method name
  token_ids = access_tokens.group(:application_id).pluck('max(id)')
  Doorkeeper::AccessToken.where(id: token_ids)
end

Hi Wasif, thanks for your response. I like your second example, all tho I would like to return the object for each group where the `resource_owner_id` == `User.id`. So imagine I have 2 groups that go by the application_id: 2 and one by application_id: 3 with both the same resource_owner_id. I would like to return the `access_token` object in that case for ID 2 and 3. — Kevin Etore, Dec 25 '17 at 09:03
`oauth_tokens.group(:application_id).pluck('MAX(id)')` works, it gives me the correct ids back like I wanted as I mentioned above. Now I just have to figure out how to return the whole object instead of the ID only, that will save me from some unnecessary db queries, thanks Wasif! — Kevin Etore, Dec 25 '17 at 10:05

Select highest ID from group

1 Answers1

Update