# ActiveRecord query using association

**URL:** <https://www.ruby-forum.com/t/activerecord-query-using-association/106757>\
**Category:** Rails\
**Created:** [June 13, 2007, 9:27pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757 "2007-06-13T21:27:21Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 13, 2007, 9:27pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/1 "2007-06-13T21:27:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 13, 2007, 11:33pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/2 "2007-06-13T23:33:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 14, 2007, 10:35pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/3 "2007-06-14T22:35:56Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 15, 2007, 3:50am UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/4 "2007-06-15T03:50:24Z")

</div>

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:

> **[Rails Single Table Inheritance](http://juixe.com/techknow/index.php/2006/06/03/rails-single-table-inheritance/)**
>
> Ruby on Rails supports Single Table Inheritance. Single Table Inheritance allows you to have one single SQL table, such as an employees table, manage different type of employees like managers and d…

may help.

Kevin W. wrote:

> The association is currently only one way.

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 15, 2007, 4:09am UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/5 "2007-06-15T04:09:20Z")

</div>

On 6/15/07, Kevin W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 15, 2007, 4:14pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/6 "2007-06-15T16:14:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![inspire22](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a4c791/32.png) [@inspire22](https://www.ruby-forum.com/u/inspire22)\
**Post date:** [June 15, 2007, 4:50pm UTC](https://www.ruby-forum.com/t/activerecord-query-using-association/106757/7 "2007-06-15T16:50:10Z")

</div>

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](http://javathehutt.blogspot.com)