CODEWITHSUNDEEP
ruby-on-railsactiverecordjoin
Chazu
Chazu

Reputation: 1018

Referencing an associated table in an ActiveRecord where/join call

Lets say I have two Rails models, Users and Lists. Users have_many lists and lists belong_to a user.

Users also have one particular list they are working on at any given time. This is denoted by the active_list_id foreign key on the User model.

Let's say I want to run the equivalent of the following query via ActiveRecord:

SELECT * FROM users JOIN lists 
ON lists.id = users.active_list_id

Basically I am trying to write a method which will get each user, along with her currently active list, to display statistics on list completion.

The solution seems to me to be something like

Users.joins(:lists).where("users.active_list_id  = lists.id")

But I can't seem to get that to work**, and I'm not sure what the most idiomatically correct solution is in Rails.

How should I pull this data most efficiently and effectively using ActiveRecord?

Thanks in advance for any help you can offer.

EDIT: The above query does work, but does not seem to pull a conjunction of the user record and its associated list.

Upvotes: 2

Views: 4641

Answers (2)

elijah
elijah

Reputation: 2924

try this:

Users.includes(:lists).where("users.active_list_id  = lists.id")

what you're doing in your question is loading the users that match that criteria, while what you want is to eager-load the association as well

Though really, you might want to consider making this a separate association:

class User
  belongs_to :active_list, :class_name => :list
end

class List
  has_one :user
end

Upvotes: 1

davidb
davidb

Reputation: 8954

There are some strangr things on your post. First thing is: "have_many" thats wrong its called has_many that might be a problem if its in your model like you wrote it down! Then the other thing is the name of the foreign key! The foreign key by convention is the name of the association in singular with addition of _id. When you say you want to hasv user.lists the foreign key must be called list_id and not active_list_id! You can specify an not conventional foreign key by passing the foreign_key option in the association! That would look like this:

User.rb:

has_many :lists, :foreign_key => 'active_list_id'

List.rb:

belongs_to :user, :foreign_key => 'active_list_id'

Then you would be able to say it like this:

User.find(<id>).lists

You NEVER EVER join tables in rails using SQL! Dont try to transfer PHP knowlage to RoR 1 to 1, just dont!

// Ahh now I understand what you ceed is a scope with parameters! User.rb:

scope :active_list, lambda{|list_id| where(:active_list_id =>  list_id)}

Then you could call User.active_list(<list_id>) to get all the users using this list!

Upvotes: 1

Related Questions