Get a list of first record for each group

Question

I have a Log model with following columns:

["id", "username", "event", "parameters", "extras", "created_at", "updated_at"]

Now, I would like to get the first log for each username ordered by created_at.

One way to do this is to run the following query for each username:

log = Log.where("username = :username", username: username).order(:created_at).first

But this obviously queries the database a lot of times (equal to the number of usernames). Is there some way to do only one database query?

Answer 1

I think group by clause would do the trick.

Log.group(:username).order(:created_at)

This will give you a query like this:

SELECT `logs`.* from `logs` GROUP BY username ORDER BY `logs`.`created_at` ASC

which will return first record for each username.

Answer 2

Log.select("DISTINCT username").order(:created_at)

Answer 3

Another case for DISTINCT ON:

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

Gives you the whole row of 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

I am not a Ruby expert, but this Ruby syntax should work:

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