thr3ads.net - Rails - Complex sql query. How to do with has_many and belongs

If this information is useful, please help other people find it:
Share via:

Webdesign Porto

2010-Mar-03 04:41 UTC

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! ;)
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

tommy xiao

2010-Mar-03 05:44 UTC

head link

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

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 Porto <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
> 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! ;)
> --
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>

-- 
tommy xiao
E-mail: xiaods(AT)gmail.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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Conrad Taylor

2010-Mar-03 07:22 UTC

head link

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

On Tue, Mar 2, 2010 at 8:41 PM, Webdesign Porto
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:
> 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! ;)
>
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

--> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Colin Law

2010-Mar-03 10:50 UTC

head link

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

On 3 March 2010 04:41, Webdesign Porto
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:> 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.
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

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Webdesign Porto

2010-Mar-03 13:39 UTC

head link

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

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



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
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Webdesign Porto

2010-Mar-03 13:47 UTC

head link

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

@ 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, :P
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Webdesign Porto

2010-Mar-03 20:34 UTC

head link

Re: 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))) 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
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Rails - Mar 2010 - Complex sql query. How to do with has_many and belongs_to?

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

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

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

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

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

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

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