ActiveRecord query using association

Hello all,

I apologize if the answer to this question is going to be rudimentary, but I am thus far stymied by the information I have found on the web. This seems like it should be simple, but I haven't really found an example of how to do this yet.

I have a model called Account which has a belongs_to reference to another model, User, as below:

class Account < ActiveRecord::Base   belongs_to :primary_user, :class_name => "User", :foreign_key => "primary_user" end

The User model has a property "last_name" and the Account model has a property "account_name". I want to create a query to find all accounts by the account's name (account.account_name) and by the primary user's last name (account.primary_user.last_name). Is there a best-practice way to write a query like this?



The association is currently only one way. An Account has many users, but only one primary (administrative) user.

Account has_many :users belongs_to :primary_user, :class=> "User", :foreign_key => "primary_user"

User belongs_to :account # no reference to an account for which this user is the primary/ administrative user

I am trying to find all accounts where the account name equals some query parameter, and the account's primary user's last name equals a query parameter. If I was going to do this in Java with Hibernate, the query would look like this:

Criteria accountCriteria = session.createCriteria(Account.class); // Create restriction to search on the account's name accountCriteria.add("accountName", searchParam1+"%")); //Create restriction on the account's primary user and search on the user's last name in addition to the Account's name Criteria userCriteria = accountCriteria.createCriteria("primaryUser"); userCriteria.add("lastName", searchParam2+"%")); List results = accountCriteria.list();

And this is not a legacy schema, and it is quite possible that it sucks. I am historically a Java guy who thinks in object models and creates a schema that exists only to persist the object model. If you have any thoughts on how to model this relationship more easily, I am all ears.

Thanks for responding, hopefully this provides enough clarification.


There is nothing stopping you from havning a has_many in the user class to use the scopeing that comes for free when you use the user object to start you queries.

I find that using an objects association collection to scope the objects to that user is one of the easier ways to do it in rails. @user.collection_of_things

class User < AR:Base

has_many :primary_accounts, :class => ‘Account’, :foreign_key => ‘primary_user’


This way you can use the user to find all the primary accounts they own


Find a particular account name for that user i believe is someting like

@user.primary_accounts.find_all_by_name( ‘name’ )

or{ |a| == ‘name’ }

Or putting it all together

@user = User.find_by_last_name( ‘last_name’ ) ||

@user.primary_accounts.find_all_by_name( ‘name’ )

I’m sure there are other ways to get to this from the account side. If I had ruby available at work I would find one :wink: But this should work.

Hope this is on track for you and helps a bit.



Thanks for the responses, all - I appreciate the timely responses from the community.

That approach works, but you then run into the N+1 query issue do you not?. Every time I need to search for an account, I also need to search for a user. Probably it won't make much difference with the size of my app, but if anyone knows a way to get this done in a single query, I feel like that would probably be best. But that approach will work, so thanks for the tip.


Account.find(:all, :conditions => [' = ? and
users.last_name = ?', 'account name', 'last name'], :include
=> :primary_user)

Or leave the table names off of the column values if they won't be
ambiguous. I only put them there because I don't know your schema.