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
Let’s clarify.
Account belongs_to User
User has_many Accounts
Yes?
Are you trying to find the names of all accounts for a User, after
having found a User by last name?
On a side note, is this a legacy database? If it’s not, I don’t think
your database is built correctly, or at least, it’s built in a way that
doesn’t leverage Rails’ standards, and therefore makes this much harder
than it needs to be.
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
On Jun 13, 4:33 pm, Brian A. [email protected]
I see where you’re going here. I think you may want to look at single
table inheritance. I don’t have experience with it, but something like
this:
may help.
Kevin W. wrote:
The association is currently only one way.
On 6/15/07, Kevin W. [email protected] wrote:
User
// 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();
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 
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