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?

Thanks,

Kevin

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(Restrictions.like("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(Restrictions.like("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.

-Kevin

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’

end

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

@user.primary_accounts

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

@user.primary_accounts.find_all_by_name( ‘name’ )

or

@user.primary_accounts.select{ |a| a.name == ‘name’ }

Or putting it all together

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

@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.

Cheers

Daniel

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.

-Kevin

Account.find(:all, :conditions => ['accounts.name = ? 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.

-Michael
http://javathehutt.blogspot.com