Complex sql query. How to do with has_many and belongs_to?

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! :wink:

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.

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! :wink:

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

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

First of all, thanks for your fast reply :slight_smile:

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 :smiley:

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

@ 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, :wink:

@ Tommy Xiao: I will look deeply in what you said after i write this post, and try it. thanks, :stuck_out_tongue:

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? :frowning:

thanks, for your time