2

I have a Log model with following columns:

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

I have indexes created_at and username.
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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Peeyush
  • 6,144
  • 5
  • 23
  • 37
  • It is important to set ONE index on 2 columns, a.g.: "add_index :logs, [:username, :created_at]". If you have indexes for each columns alone, it doesn't work. – Exsemt Jul 08 '14 at 16:18

3 Answers3

5

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:

Similar answer for Ruby / AR / Postgres:

How to execute raw SQL:

This Ruby syntax should work:

Log.select("DISTINCT ON (username) *").order(:username, :created_at)
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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

Rafal
  • 2,576
  • 2
  • 18
  • 13
  • I don't want a list of usernames! I am interested in other columns (`parameters` and `extras`) for first record in each group as mentioned in question. – Peeyush Jul 08 '14 at 16:14
0

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.

San
  • 1,954
  • 1
  • 14
  • 18
  • 1
    It does not return the first log in PostgreSQL. I think it does so in MySQL. Thannx anyway. :) – Peeyush Jul 08 '14 at 16:41