CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
Lovish Choudhary
Lovish Choudhary

Reputation: 167

Converting sql query to ActiveRecord Rails

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 ?

Upvotes: 0

Views: 264

Answers (2)

Fred Willmore
Fred Willmore

Reputation: 4624

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.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 0

Related Questions