CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Rafael Fragoso
Rafael Fragoso

Reputation: 1309

How to transform this SQL Query into Rails Active Record?

I have the following query:

SELECT * FROM users 
JOIN locations 
ON locations.id = (
    SELECT id from locations 
    WHERE locations.user_id = users.id 
    ORDER BY locations.created_at 
    desc LIMIT 1
)

And this basically means that I have 2 models (User, Locations) in which User has_many Locations.

The result I expect is: For each user, I want to pull their last location.

How can I do that using only ActiveRecord?

Upvotes: 0

Views: 188

Answers (3)

tekuri
tekuri

Reputation: 146

You can try this: 

 User.where("id in (SELECT user_id from locations l 
        INNER JOIN users u ON u.id = l.user_id ORDER BY l.created_at desc LIMIT 1)")

Upvotes: 0

PGill
PGill

Reputation: 3521

You can create a has_one association 

class User
  has_one :last_location, -> { order 'created_at DESC' }, class_name: "Location"
end

Then you can do 

users = User.includes(:last_location)
users.each { |u| u.last_location; ... }

Upvotes: 3

treiff
treiff

Reputation: 1306

I think the simplest you could do this is by adding a scope on your Location model to return the most recent location.

While this doesn't give you the same SQL as you wrote manually it should provide the same results.

location.rb

scope :most_recent, -> { order(created_at: :desc).limit(1) }

Then something like:

user = User.last
user.locations.most_recent

This should give you this SQL output which will return the most recently created location for the user.

SELECT  "locations".* FROM "locations" WHERE "locations"."user_id" = $1  ORDER BY "locations"."created_at" DESC LIMIT 1

Upvotes: 1

Related Questions