CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecorddistinct-on
Mike Neumegen
Mike Neumegen

Reputation: 2486

Most recent inner query in activerecord

I have a table users:

id  first_name
--------------
1   Bill
2   Denise

who read multiple books:

id  book                       user_id  read_at
---------------------------------------------------
1   Garry Potter               1        2020-1-1
2   Lord of the wrist watch    2        2020-1-1
3   90 Shades of navy          2        2020-1-2

I want to create a scope in my book model that gets me the latest book for each user. There's plenty examples of doing this with pure SQL, the problem I'm running to is creating a flexible scope that can be used with a count, inner query or any other way you would typically use a scope.

So far I have this in my book model:

def self.most_recent
  inner_query = select('DISTINCT ON (user_id) *').order(:user_id, read_at: :desc)
  select('*').from(inner_query, :inner_query).order('inner_query.id')
end

Which is very close to what I want. It works with a count however not in a more complicated situation.

For example if I want to get a list of users where their latest book is "Garry Potter", I try something like this:

User.where(id: Book.most_recent.where(book: 'Garry Potter').select(:user_id))

Active record gets confused and generates this SQL:

SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT "user_id", * FROM (SELECT "books"."user_id", DISTINCT ON (user_id) * FROM "books" ORDER BY "books"."user_id" ASC, "books"."read_at" DESC) inner_query WHERE "books"."book" = "Garry Potter" ORDER BY inner_query.id)

Which gives the following error:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "DISTINCT"

Is there an elegant way to achieve this?

Upvotes: 4

Views: 67

Answers (1)

pedrosfdcarneiro
pedrosfdcarneiro

Reputation: 341

You can try change the method most_recent by returning a query with where:

def self.most_recent
  # Select only the ids of the most recent books
  inner_query = select('DISTINCT ON (user_id) books.id').order(:user_id, read_at: :desc)

  # Return a where query like Book.where(id: <ids in the result set of the query above>)
  where(id: inner_query)
end

# You should now be able to perform the following query
User.where(
  id: Book.most_recent.where(book: 'Garry Potter').select(:user_id)
)

Upvotes: 1

Related Questions