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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Ablaza
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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.
>
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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: http://www.juixe.com/techknow/index.php/2006/06/03/rails-single-table-inheritance/ may help. Kevin Whinnery wrote:> The association is currently only one way.-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 6/15/07, Kevin Whinnery <Kevin.Whinnery-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > 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();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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Jun 14, 9:08 pm, "Daniel N" <has....-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 6/15/07, Kevin Whinnery <Kevin.Whinn...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > > 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(); > > 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Jun 15, 2007, at 7:13 AM, Kevin Whinnery wrote:> > 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 > > On Jun 14, 9:08 pm, "Daniel N" <has....-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On 6/15/07, Kevin Whinnery <Kevin.Whinn...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> >> >> >> >>> 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(); >> >> 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 > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---