CODEWITHSUNDEEP
rubyruby-on-rails-3activerecord
Chirag Rupani
Chirag Rupani

Reputation: 1715

Getting latest record for each userid in rails 3.2

I have user with name, location, created_at as important fields in table. I want to retrieve for each user the latest location,i.e, I want something like this:

username    location       created_at
abc         New York       2012-08-18 16:18:57
xyz         Mexico city    2012-08-18 16:18:57
abc         Atlanta        2012-08-11 16:18:57

only input is UId(1,2) array of userids.please help me to accomplish this.I just want to know how to write query using active record query interface.

Upvotes: 0

Views: 127

Answers (3)

Mladen Jablanović
Mladen Jablanović

Reputation: 44080

Generally, this should be a standard way to solve this kind of problems:

SELECT l1.user, l1.location
FROM locations l1
LEFT JOIN locations l2 ON l1.user = l2.user AND l2.created_at > l1.created_at
WHERE l2.id IS NULL

The idea is to join the table with itself, and find those rows which don't have any row with the same user and greater created_at.

Of course, you should have (user, created_at) index on your table.

Now you should see how would that be represented in AR interface.

Upvotes: 2

AnkitG
AnkitG

Reputation: 6568

You can Use

User.where(:uid => [1,2,3]).maximum('location')

which will create something like

SELECT MAX(`users`.`location`) AS max_id FROM `users` WHERE `users`.`id` IN (1, 2,3)

Upvotes: 0

Atastor
Atastor

Reputation: 741

When 

u_id

is the array of user ids, then 

u_id.map{|i| User.find(i).location}

should be an array of the users locations.

Upvotes: 0

Related Questions