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