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.

2010/3/3 Webdesign P. [email protected]

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 [email protected]
To unsubscribe from this group, send email to
[email protected][email protected]
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.


tommy xiao
E-mail: xiaods(AT)gmail.com

On Tue, Mar 2, 2010 at 8:41 PM, Webdesign P.
[email protected]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! :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

On 3 March 2010 04:41, Webdesign P. [email protected] 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

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

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

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs