Converting sql query to ActiveRecord Rails

Question

I have a location table in my database which contains location data of all the users of my system.

The table design is something like

 id| user_id| longitude| latitude| created_at|

I have an array of users. Now I want to select the latest(sorted according to created at) location of all these users.

I am able to figure out the sql query for same

SELECT * FROM   my_table
WHERE (user_id , created_at) IN (
                SELECT   user_id, MAX(created_at)
                FROM     my_table
                GROUP BY user_id
            )
AND user_id IN ('user1', 'user2', ... );

Now as I am working in Ruby On Rails, I want to write this sql query to activerecord rails. Can anyone please help me with this ?

Fred Willmore · Accepted Answer · 2016-05-27 18:28:55Z

1

I think this will give the correct result:

MyModel.order(created_at: :desc).group(:user_id).distinct(:user_id)

If you want to generate the exact same query, this will do it:

MyModel.where("(user_id, created_at) IN (SELECT user_id, MAX(created_at) from my_table GROUP BY user_id)")

I think the subquery will probably not scale well with a large data set, but I understand if you just want to get it into rails and optimize later.

edited May 27, 2016 at 18:28

answered May 27, 2016 at 17:23

Fred Willmore

4,6461 gold badge33 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lovish Choudhary Over a year ago

This is not generating the same sql query SELECT DISTINCT "my_models".* FROM "my_models" GROUP BY "my_models"."user_id", "my_models"."id" ORDER BY "my_models"."created_at" DESC

Fred Willmore Over a year ago

I added an edit that should reproduce the existing query exactly - let me know how it goes!

David Aldridge · Accepted Answer · 2016-05-27 16:46:10Z

0

How about adding a scope, and getting the same result in a slightly different way:

class UserLocation

  def self.latest_per_user
    where("user_locations.created_at = (select Max(ul2.created_at) from user_locations ul2 where ul2.user_id = user_locations.user_id)")
  end

end

Then you just use:

UserLocation.latest_per_user.where(:user_id => ['user1', 'user2'])

... to get the required data set.

answered May 27, 2016 at 16:46

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Collectives™ on Stack Overflow

Converting sql query to ActiveRecord Rails

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related