How to Get the Latest Record from Each Group in Activerecord

How to get the latest record from each group in ActiveRecord?

Postgres

In Postgres, this can be achieved with the following query.

SELECT DISTINCT ON ("group") * FROM projects
ORDER BY "group", date DESC, id DESC

Because the date column might not be unique here, I have added an additional ORDER BY clause on id DESC to break ties in favor of the record with the higher ID, in case two records in a group have the same date. You might instead want to use another column like the date/time of the last update or so, that depends on your use case.

Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:

Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc)

or if you prefer using ARel instead of having raw SQL:

p = Project.arel_table
Project.find_by_sql(
  p.project(p[Arel.star])
   .distinct_on(p[:group])
   .order(p[:group], p[:date].desc, p[:id].desc)
)

MySQL

For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.

How to get the last nth record for each group in a rails query

To find the nth last game for a specific player, it would probably be easiest to start by finding the player.

player = Player.find_by(name: "user3692508")

then you can find the players games with:

player.games

To get the nth last game, you can order it by games_played in descending order, then limit it to one result and offset it with the offset you want:

player.games.order(games_played: :desc).limit(1).offset(0)

If you do an offset of 0, you will get the last game. If you do an offset of 1 you will get the 2nd last game and so on.

This is assuming your player has_many :games and the game belongs_to :player

By using a sub query you can get the nth last game for each player.(it looks like a mess.....)

offset = 1

sub_query = "SELECT 'sub_game'.id FROM 'games' as sub_game WHERE 'sub_game'.'player_id' = games.player_id ORDER BY 'sub_game'.'games_played' DESC LIMIT 1 OFFSET #{offset}"

Game.joins(:player).where("games.id IN (#{sub_query})").order(id: :desc).group(:player_id)

With this solution, you would sort the games for each player in the sub query and do the offset there first.

SQL query to get last record based on table relation

you can do this query (Result here)

with x as (
select row_number() over (partition by p.id order by b.created_at desc) as rn,b.id as id_box,p.id as id_paper
from boxes b join stones s on b.stone_id = s.id
join papers p on p.id = s.paper_id)
select x.id_box from x where rn = 1

Rails show latest value after grouping

@servers = Server.order(created_at: :desc).group(:server_id)

ActiveRecord return the newest record per user (unique)

I've found one solution that is suboptimal performance-wise but will work for very small datasets, when time is short or it's a hobby project:

Card.all.order(:user_id, :created_at).to_a.uniq(&:user_id)

This takes the AR:Relation results, casts them into a Ruby Array, then performs a Array#uniq on the results with a Proc. After some brief testing it appears #uniq will preserve order, so as long as everything is in order before using uniq you should be good.

The feature is time sensitive so I'm going to use this for now, but I will be looking at something in raw SQL following @Gene's response and link.

How to get the last entered record field in rails with join and group by?

I think it's your group_by. That's a vanilla ruby enum. Have you tried .group('jobs.id')? group is an activerecord query helper that will run the sql group by clause

top n record of each group by active record rails

I would do something like this:

Result.find_by_sql("SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY source_id ORDER BY order DESC) AS r, results.* FROM results) x WHERE x.r <= 2 ORDER BY order")

Get a list of first record for each group

Another case for DISTINCT ON:

SELECT DISTINCT ON (username) *
FROM   log
ORDER  BY username, created_at;

Returns the whole row for the "first" entry per username.

Details:

• Select first row in each GROUP BY group?

Similar answer for Ruby / AR / Postgres:

• Display latest messages from messages table, group by user

How to execute raw SQL:

• Table join sql to rails active record query

This Ruby syntax should work:

Log.select("DISTINCT ON (username) *").order(:username, :created_at)

how do you find the latest row for each user in rails?

Rails#ActiveRecord:

LessonPlan.select([:user_id, :id, 'MAX(created_at)']).group(:user_id).limit(10)

MySql :

select id, user_id, MAX(created_at) from lesson_plans group by user_id limit 10

---

Related Topics