# Complex sql query. How to do with has\_many and belongs\_to? **URL:** \ **Category:** Rails\ **Created:** [March 3, 2010, 5:41am UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475 "2010-03-03T05:41:44Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 5:41am UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/1 "2010-03-03T05:41:44Z")
SELECT profiles.\*, friendships.updated\_at AS friendship\_updated\_at, friendships.profile\_id, friendships.friend\_id, friendships.accepted AS friendship\_accepted, friendships.rejected AS friendship\_rejected FROM `profiles` INNER JOIN `friendships` ON `profiles`.id = `friendships`.profile\_id WHERE (((`friendships`.profile\_id = 1) OR (`friendships`.friend\_id = 1)) AND ((`friendships`.`accepted` = 0))) ORDER BY friendships.updated\_at LIMIT 0, 10 more or less, this is the query. How do I make it a has many with belongs\_to? I really dont know how to do it… :s im working on it for 3-4 days… and still no answer. I dont use to ask for help in forums, but now… i really need some help here. Thanks! 😉 ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 6:46am UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/2 "2010-03-03T06:46:07Z")
Maybe first in model,create find way function.the sql string looks likely not complicated in my view. use condition.blow find doc is in rails 2.3.x manule - :joins - Either an SQL fragment for additional joins like “LEFT JOIN comments ON comments.post\_id = id” (rarely needed), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations. If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table’s columns. Pass :readonly =\> false to override. - :include - Names associations that should be loaded alongside. The symbols named refer to already defined associations. See eager loading under Associations. i think you should use include to implement your request. 2010/3/3 Webdesign P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > I really dont know how to do it… :s im working on it for 3-4 days… > To post to this group, send email to removed\_email\_address@domain.invalid. > To unsubscribe from this group, send email to > removed\_email\_address@domain.invalid[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > . > For more options, visit this group at > [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en). – tommy xiao E-mail: xiaods(AT)[gmail.com](http://gmail.com) ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 8:23am UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/3 "2010-03-03T08:23:49Z")
On Tue, Mar 2, 2010 at 8:41 PM, Webdesign P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)wrote: > I really dont know how to do it… :s im working on it for 3-4 days… > and still no answer. I dont use to ask for help in forums, but now… i > really need some help here. > > Thanks! 😉 Hi, I understand the query but I’m not clear as to your exact question? Can you post the relevant models for the above and provide some information as to what you’re wanting to do? -Conrad – ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 11:52am UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/4 "2010-03-03T11:52:11Z")
On 3 March 2010 04:41, Webdesign P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I really dont know how to do it… :s im working on it for 3-4 days… > and still no answer. I dont use to ask for help in forums, but now… i > really need some help here. Don’t think about the query first, think about what your models are and how they are related. Then decide what information you need from the db in terms of those relationships not in terms of SQL. Using rails you should only rarely need to worry about SQL, that is a major part of the magic of rails. Colin ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 2:47pm UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/5 "2010-03-03T14:47:38Z")
@ Colin and @Conrad: Ah, yes, what I really want is to get my friends and friendships column looking for an id in friendships.profile\_id or friendships.friend\_id. The code is in the other post. thanks, 😉 @ Tommy Xiao: I will look deeply in what you said after i write this post, and try it. thanks, 😛 ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 2:39pm UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/6 "2010-03-03T14:39:09Z")
First of all, thanks for your fast reply 🙂 Currently Im using a profile and friendship model with a modified version of acts\_as\_network(in the end of the reply). This is really bad code, and the query they provide to me when I want friends is not exactly what I want. This queries 2 times the friendship table: first looks for all the profile\_id column and then another query looks for friend\_id column, and in this way is hard to sort, limit and offset data. I would like to make a single sql request that looks for in the friend\_id OR profile\_id for that id. In this way it’s easy to offset, limit and sort my data. So… this is the code I’m talking about 😃 Profile model: has\_many :friendships acts\_as\_network :friends, :through =\> :friendships, :foreign\_key =\> ‘profile\_id’, :association\_foreign\_key =\> ‘friend\_id’, :conditions =\> {‘friendships.accepted’ =\> true} acts\_as\_network :friends\_pending, :through =\> :friendships, :foreign\_key =\> ‘profile\_id’, :association\_foreign\_key =\> ‘friend\_id’, :conditions =\> {‘friendships.accepted’ =\> false} Frienship model: belongs\_to :profile belongs\_to :profile\_target, :class\_name =\> ‘Profile’, :foreign\_key =\> ‘friend\_id’ Acts as network modified: def acts\_as\_network(relationship, options = {}) configuration = { :foreign\_key =\> name.foreign\_key, :association\_foreign\_key =\> “#{name.foreign\_key}_target", :join\_table =\> "#{name.tableize}_#{name.tableize}”, :order =\> ‘friendships.updated\_at’, :limit =\> 1, :offset =\> 2 } configuration.update(options) if options.is\_a?(Hash) ``` if configuration[:through].nil? has_and_belongs_to_many "#{relationship}_out".to_sym, ``` :class\_name =\> name, :foreign\_key =\> configuration[:foreign\_key], :association\_foreign\_key =\> configuration[:association\_foreign\_key], :join\_table =\> configuration[:join\_table], :conditions =\> configuration[:conditions] ``` has_and_belongs_to_many "#{relationship}_in".to_sym, ``` :class\_name =\> name, :foreign\_key =\> configuration[:association\_foreign\_key], :association\_foreign\_key =\> configuration[:foreign\_key], :join\_table =\> configuration[:join\_table], :conditions =\> configuration[:conditions] ``` else through_class = configuration[:through].to_s.classify through_sym = configuration[:through] # a node has many outbound realationships has_many "#{through_sym}_out".to_sym, :class_name => ``` through\_class, :foreign\_key =\> configuration[:foreign\_key], :order =\> configuration[:order] ``` has_many "#{relationship}_out".to_sym, :through => ``` “#{through\_sym}\_out”.to\_sym, :source =\> “#{name.downcase}\_target”, :foreign\_key =\> configuration[:foreign\_key], :conditions =\> configuration[:conditions], :order =\> configuration[:order], :select =\> ‘profiles.\*, friendships.updated\_at AS friendship\_updated\_at, friendships.profile\_id, friendships.friend\_id, friendships.accepted AS friendship\_accepted, friendships.rejected AS friendship\_rejected’ ``` # a node has many inbound relationships has_many "#{through_sym}_in".to_sym, :class_name => ``` through\_class, :foreign\_key =\> configuration[:association\_foreign\_key], :order =\> configuration[:order] ``` has_many "#{relationship}_in".to_sym, :through => ``` “#{through\_sym}\_in”.to\_sym, :source =\> name.downcase, :foreign\_key =\> configuration[:association\_foreign\_key], :conditions =\> configuration[:conditions], :order =\> configuration[:order], :select =\> ‘profiles.\*, friendships.updated\_at AS friendship\_updated\_at, friendships.profile\_id, friendships.friend\_id, friendships.accepted AS friendship\_accepted, friendships.rejected AS friendship\_rejected’ ``` # when using a join model, define a method providing a ``` unioned view of all the join # records. i.e. if People acts\_as\_network :contacts :through =\> :invites, this method # is defined as def invites class\_eval \<\<-EOV def #{through\_sym} UnionCollection.new(self.#{through\_sym}\_in, self.#{through\_sym}\_out) end EOV ``` end # define the accessor method for the reciprocal network ``` relationship view itself. # i.e. if People acts\_as\_network :contacts, this method is defind as def contacts class\_eval \<\<-EOV def #{relationship} UnionCollection.new(self.#{relationship}\_in, self.#{relationship}\_out) end EOV end ---
**Author:** ![webdesignporto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/db5fbb/32.png) [@webdesignporto](https://www.ruby-forum.com/u/webdesignporto)\ **Post date:** [March 3, 2010, 9:34pm UTC](https://www.ruby-forum.com/t/complex-sql-query-how-to-do-with-has-many-and-belongs-to/184475/7 "2010-03-03T21:34:34Z")
SELECT (profiles._, friendships.updated\_at AS friendship\_updated\_at, friendships.profile\_id, friendships.friend\_id, friendships.accepted AS friendship\_accepted, friendships.rejected AS friendship\_rejected FROM `profiles` INNER JOIN `friendships` ON `profiles`.id = `friendships`.profile\_id WHERE (((`friendships`.profile\_id = 1))) AND ((`friendships`.`accepted` = 0)))) UNION SELECT (profiles._, friendships.updated\_at AS friendship\_updated\_at, friendships.profile\_id, friendships.friend\_id, friendships.accepted AS friendship\_accepted, friendships.rejected AS friendship\_rejected FROM `profiles` INNER JOIN `friendships` ON `profiles`.id = `friendships`.profile\_id WHERE (((`friendships`.friend\_id = 1))) AND ((`friendships`.`accepted` = 0)))) ORDER BY friendships.updated\_at LIMIT 0, 10 lool this is the new query I need… It’s a little bit more complicated right now… any help? ☹ thanks, for your time