RoR PostgresQL - get the latest, great values ​​from the database

Question

I am trying to query my PostgreSQL database to get the latest (by created_at) and various (by user_id) Activity objects, where each user has several actions in the database. The activity object is structured as such:

Activity(id, user_id, created_at, ...)

First I tried to execute the following query:

Activity.order('created_at DESC').select('DISTINCT ON (activities.user_id) activities.*')

Nonetheless,

kept getting the following error:

ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

According to this message: PG :: Error: SELECT DISTINCT, ORDER BY expressions should appear in the selection list , it seems that the ORDER BY clause can be applied only after applying DISTINCT. This does not help me, since I want to get various actions using user_id, but I also want the actions to be the last actions created. Thus, I need the actions to be sorted before receiving individual actions.

I came up with a solution that works, but first groups the actions by user ID, and then organizes the actions within the groups using create_at. However, this requires two queries.

I was wondering if what I want is possible in just one request?

Answer 1

,

1

Activity.select('DISTINCT ON (activities.user_id) activities.*').order('created_at DESC')

2

1, ,

activity

scope :latest, -> {
                    select("distinct on(user_id) activities.user_id,
                                activities.*").
                    order("user_id, created_at desc")
                }

,

Activity.latest

,