CODEWITHSUNDEEP
ruby-on-railspostgresql
Marcus Christiansen
Marcus Christiansen

Reputation: 187

RoR PostgresQL - Get latest, distinct values from database

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

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

I first tried to get the below query to work:

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

however, kept getting the below error:

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

According to this post: PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list, it looks like The ORDER BY clause can only be applied after the DISTINCT has been applied. This does not help me, as I want to get the distinct activities by user_id, but also want the activities to be the most recently created activities. Thus, I need the activities to be sorted before getting the distinct activities.

I have come up with a solution that works, but first grouping the activities by user id, and then ordering the activities within the groups by created_at. However, this takes two queries to do.

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

Upvotes: 4

Views: 149

Answers (1)

fool-dev
fool-dev

Reputation: 7777

This should work, try the following

Solution 1

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

Solution 2

If not work Solution 1 then this is helpful if you create a scope for this

activity model

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

Now you can call this anywhere like below

Activity.latest

Hope it helps

Upvotes: 2

Related Questions