thr3ads.net - Rails - Trying tpo do complex finds with ActiveRecord [Jul 2007]

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

Chinmay Kulkarni

2007-Jul-07 16:43 UTC

Trying tpo do complex finds with ActiveRecord

HI all!

Well, actually, "complex finds" to me because I can''t figure
how.

I have this app where I have two basic models: user and post.

There is also a model ''friendship''. This basically belongs_to
:user, and
belongs_to :friend (with :class_name=>''User''). This model
basically stores
the friends whose posts the given user wants to follow.

So to find a particular friend''s posts, I do posts
me.friendships.find(:first).friend.posts

Now, I want to find ALL my friends'' recent posts (with a created_at
filter,
or simply the latest 5 posts etc).

How do I do that? Obviously, I can look through each friend''s posts,
until I
get the 5 most recent posts (by created_at), but then if I have a lot of
friends, it means a lot of useless database queries.

Is there a better way? Someone recommended trying to do it with SQL JOIN but
a)I don''t know SQL well, b)I like ActiveRecord''s warm, fuzzy
feel.

Please help!

Yours,
Chinmay
-- 
View this message in context:
http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480071
Sent from the RubyOnRails Users mailing list archive at Nabble.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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

dblack-TKXtfPMJ4Ozk1uMJSBkQmQ@public.gmane.org

2007-Jul-07 16:59 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

Hi --
On Sat, 7 Jul 2007, Chinmay Kulkarni wrote:
> HI all!
>
> Well, actually, "complex finds" to me because I can''t
figure how.
>
> I have this app where I have two basic models: user and post.
>
> There is also a model ''friendship''. This basically
belongs_to :user, and
> belongs_to :friend (with :class_name=>''User''). This
model basically stores
> the friends whose posts the given user wants to follow.
>
> So to find a particular friend''s posts, I do posts >
me.friendships.find(:first).friend.posts
>
> Now, I want to find ALL my friends'' recent posts (with a
created_at filter,
> or simply the latest 5 posts etc).
>
> How do I do that? Obviously, I can look through each friend''s
posts, until I
> get the 5 most recent posts (by created_at), but then if I have a lot of
> friends, it means a lot of useless database queries.
>
> Is there a better way? Someone recommended trying to do it with SQL JOIN
but
> a)I don''t know SQL well, b)I like ActiveRecord''s warm,
fuzzy feel.
You can actually use find on the collection:

   me...friend.posts.find(:all, :order => "created_at DESC", :limit
=> 5)

and you''ll even get it all in one database query.  (The call to posts
returns a proxy that''s smart enough to wait until it sees whether
you''re going to do a ''find'' before it retrieves the
records.)

Or you can wrap that in an association in the User model:

   has_many :recent_posts, :class_name => "Post",
            :order => "created_at DESC", :limit => 5

Then you can do:

   me...friend.recent_posts

I might be overlooking some subtleties of the domain but hopefully
these examples will get you started.


David

-- 
* Books:
   RAILS ROUTING (new! http://www.awprofessional.com/title/0321509242)
   RUBY FOR RAILS (http://www.manning.com/black)
* Ruby/Rails training
     & consulting:  Ruby Power and Light, LLC (http://www.rubypal.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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Chinmay Kulkarni

2007-Jul-07 17:14 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

dblack,

Thanks for the reply.

BUt that''s not what I want to do. Your approach will give me the most
recent
posts of a particular friend. What I want is "Take all the posts from all
my
friends. Then pick the 5 most recent ones and show me".

Perhaps this will make my intent clearer. (Sorry I''m not always the
most
expressive person around!)

dblack wrote:> 
> 
> Hi --
> On Sat, 7 Jul 2007, Chinmay Kulkarni wrote:
> 
>> HI all!
>>
>> Well, actually, "complex finds" to me because I
can''t figure how.
>>
>> I have this app where I have two basic models: user and post.
>>
>> There is also a model ''friendship''. This basically
belongs_to :user, and
>> belongs_to :friend (with :class_name=>''User'').
This model basically
>> stores
>> the friends whose posts the given user wants to follow.
>>
>> So to find a particular friend''s posts, I do posts >>
me.friendships.find(:first).friend.posts
>>
>> Now, I want to find ALL my friends'' recent posts (with a
created_at
>> filter,
>> or simply the latest 5 posts etc).
>>
>> How do I do that? Obviously, I can look through each friend''s
posts,
>> until I
>> get the 5 most recent posts (by created_at), but then if I have a lot
of
>> friends, it means a lot of useless database queries.
>>
>> Is there a better way? Someone recommended trying to do it with SQL
JOIN
>> but
>> a)I don''t know SQL well, b)I like ActiveRecord''s
warm, fuzzy feel.
> 
> You can actually use find on the collection:
> 
>    me...friend.posts.find(:all, :order => "created_at DESC",
:limit => 5)
> 
> and you''ll even get it all in one database query.  (The call to
posts
> returns a proxy that''s smart enough to wait until it sees whether
> you''re going to do a ''find'' before it retrieves
the records.)
> 
> Or you can wrap that in an association in the User model:
> 
>    has_many :recent_posts, :class_name => "Post",
>             :order => "created_at DESC", :limit => 5
> 
> Then you can do:
> 
>    me...friend.recent_posts
> 
> I might be overlooking some subtleties of the domain but hopefully
> these examples will get you started.
> 
> 
> David
> 
> -- 
> * Books:
>    RAILS ROUTING (new! http://www.awprofessional.com/title/0321509242)
>    RUBY FOR RAILS (http://www.manning.com/black)
> * Ruby/Rails training
>      & consulting:  Ruby Power and Light, LLC (http://www.rubypal.com)
> 
> > 
> 
> 
-- 
View this message in context:
http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480356
Sent from the RubyOnRails Users mailing list archive at Nabble.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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Matthew Rudy

2007-Jul-08 22:01 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

here''s my solution.

It''s not in a single query,
but I imagine the "friendships" call will be pretty cheap..

class User
    def friend_ids
        self.friendships.map(&:friend_id)
    end

    def find_friends_posts(*args)
        Post.with_scope(:find => {:conditions => 
friends_scope_condition}           ) do
            Post.find(*args)
        end
    end

    def friends_scope_condition
        ids = self.friend_ids
        if ids.empty?
            return "0"
        else
            return ["user_id IN (?)", ids]
        end
    end

    def find_most_recent_friends_posts(num)
        self.find_friends_posts(:all, :order => "created_at DESC", 
:limit => num)
    end
end

That should work pretty cleanly,
and is slightly nicer than doing a big join.

Chinmay Kulkarni wrote:> dblack,
> 
> Thanks for the reply.
> 
> BUt that''s not what I want to do. Your approach will give me the
most
> recent
> posts of a particular friend. What I want is "Take all the posts from 
> all my
> friends. Then pick the 5 most recent ones and show me".
> 
> Perhaps this will make my intent clearer. (Sorry I''m not always
the most
> expressive person around!)
> 
> dblack wrote:
>>>
>>> or simply the latest 5 posts etc).
>> You can actually use find on the collection:
>>             :order => "created_at DESC", :limit => 5
>> 
>> -- 
>> * Books:
>>    RAILS ROUTING (new! http://www.awprofessional.com/title/0321509242)
>>    RUBY FOR RAILS (http://www.manning.com/black)
>> * Ruby/Rails training
>>      & consulting:  Ruby Power and Light, LLC
(http://www.rubypal.com)
>> 
>> > 
>> 
>> 
> 
> --
> View this message in context: 
>
http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480356
> Sent from the RubyOnRails Users mailing list archive at Nabble.com.

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

Chinmay Kulkarni

2007-Jul-10 04:14 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

Hey, that seems to be a good solution. No matter that it is two queries, at
least, it is O(1), not O(n).
Thanks a ton, kaps.




Kaps Lok wrote:> 
> 
> here''s my solution.
> 
> It''s not in a single query,
> but I imagine the "friendships" call will be pretty cheap..
> 
> class User
>     def friend_ids
>         self.friendships.map(&:friend_id)
>     end
> 
>     def find_friends_posts(*args)
>         Post.with_scope(:find => {:conditions => 
> friends_scope_condition}           ) do
>             Post.find(*args)
>         end
>     end
> 
>     def friends_scope_condition
>         ids = self.friend_ids
>         if ids.empty?
>             return "0"
>         else
>             return ["user_id IN (?)", ids]
>         end
>     end
> 
>     def find_most_recent_friends_posts(num)
>         self.find_friends_posts(:all, :order => "created_at
DESC",
> :limit => num)
>     end
> end
> 
> That should work pretty cleanly,
> and is slightly nicer than doing a big join.
> 
> Chinmay Kulkarni wrote:
>> dblack,
>> 
>> Thanks for the reply.
>> 
>> BUt that''s not what I want to do. Your approach will give me
the most
>> recent
>> posts of a particular friend. What I want is "Take all the posts
from
>> all my
>> friends. Then pick the 5 most recent ones and show me".
>> 
>> Perhaps this will make my intent clearer. (Sorry I''m not
always the most
>> expressive person around!)
>> 
>> dblack wrote:
>>>>
>>>> or simply the latest 5 posts etc).
>>> You can actually use find on the collection:
>>>             :order => "created_at DESC", :limit =>
5
>>> 
>>> -- 
>>> * Books:
>>>    RAILS ROUTING (new!
http://www.awprofessional.com/title/0321509242)
>>>    RUBY FOR RAILS (http://www.manning.com/black)
>>> * Ruby/Rails training
>>>      & consulting:  Ruby Power and Light, LLC
(http://www.rubypal.com)
>>> 
>>> > 
>>> 
>>> 
>> 
>> --
>> View this message in context: 
>>
http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480356
>> Sent from the RubyOnRails Users mailing list archive at Nabble.com.
> 
> 
> -- 
> Posted via http://www.ruby-forum.com/.
> 
> > 
> 
> 
-- 
View this message in context:
http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11514626
Sent from the RubyOnRails Users mailing list archive at Nabble.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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Lionel Bouton

2007-Jul-10 09:53 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

Matthew Rudy wrote:> here''s my solution.
>
> [...]
>
> That should work pretty cleanly,
> and is slightly nicer than doing a big join.
>
>   
Nicer for the coder (if you don''t know SQL very well), but slower than
a
join (both for the DB and the Ruby process), the actual amount of 
''slower'' can be negligible though and depends mainly on the
number of
intermediates. The reasons for the relative slowness :
- latency between DB and Ruby is doubled (1 query even doing and 
returning nothing takes time),
- DB can''t optimize the join itself because it sees 2 unrelated
queries,
- DB must return more data,
- Ruby must create more objects.

Rewriting with a join :

class User
  def find_friends_posts(*args)
    Post.with_scope(:find => friends_scope) do
      Post.find(*args)
    end
  end

  def friends_scope
    {
      # You want posts matching a friend of someone
      :joins => "LEFT JOIN friendships ON friendships.friend_id =
posts.user_id",
      # Here the ''someone'' is myself
      :conditions => [ "friendships.user_id = ?", self.id ]
    }
  end

  def find_most_recent_friends_posts(num)
    self.find_friends_posts(:all, :order => "created_at DESC",
:limit => num)
  end
end


Not as clear as the ''Ruby doing the join for me'', but not so
bad (in
fact you don''t have to code the " ''0'' for the no
friend case" so for
someone familiar with SQL it can look nicer). And you better know this 
way when you have the same problem to solve but with thousands of 
intermediates (friends here) if you don''t wand the query to take
ages...

Regards,

Lionel

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

Matthew Rudy

2007-Jul-10 11:31 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

That''s fair enough,
but last time I did that I had two problems,

1. :joins doesn''t scope properly
2a. doing a join and not specifying the :select means that ambiguous 
columns, eg. "id" get overwritten in the returned models
2b. :select keys dont get scoped properly.

The solution may be this instead;

def friends_scope
    {
      :conditions => [ "EXISTS (SELECT * FROM friendships f WHERE 
f.friend_id = posts.user_id AND f.user_id = ?)", self.id ]
    }
  end
> 
>   def friends_scope
>     {
>       # You want posts matching a friend of someone
>       :joins => "LEFT JOIN friendships ON friendships.friend_id = 
> posts.user_id",
>       # Here the ''someone'' is myself
>       :conditions => [ "friendships.user_id = ?", self.id ]
>     }
>   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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Lionel Bouton

2007-Jul-10 12:50 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

Matthew Rudy wrote:> That''s fair enough,
> but last time I did that I had two problems,
>
> 1. :joins doesn''t scope properly
>   
a :joins isn''t enough, you must add the proper :conditions if
that''s
what you mean (but I suspect I don''t get your meaning, I rarely use 
scope and never used it with joins, so I may miss something).
> 2a. doing a join and not specifying the :select means that ambiguous 
> columns, eg. "id" get overwritten in the returned models
>   
Hum, happened to me too (funny that ActiveRecord doesn''t automatically 
add the proper select when a joins exists, I still use 1.1.6 so it may 
have been added since then). :select => ''posts.*'' should be
enough in
the scope. In some cases you might have to deduplicate entries and use 
''DISTINCT posts.*'', but not in this particular case.
> 2b. :select keys dont get scoped properly.
>
>   
? I didn''t even try using them in a scope yet, care to elaborate?

Lionel

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

Matthew Rudy

2007-Jul-10 13:24 UTC

head link

Re: Trying tpo do complex finds with ActiveRecord

actually, you''re right.
:joins now forces :select => table_name+".*"
but only if :joins is supplied in the options, not in the scope.

================================================================sql =
"SELECT #{(scope && scope[:select]) || options[:select] ||
(options[:joins] && table_name + ''.*'') ||
''*''} "
================================================================
equally :joins either takes the scoped version, or the direct version

================================================================def
add_joins!(sql, options, scope = :auto)
  scope = scope(:find) if :auto == scope
  join = (scope && scope[:joins]) || options[:joins]
  sql << " #{join} " if join
end
================================================================
so, for this simple purpose we''re fine,
as the join and select would only be specified once (in the scope)
but if you want to layer multiple :joins it doesn''t work.

I wrote a patch a while back,
but never found the time to submit it.

Lionel Bouton wrote:> Matthew Rudy wrote:
>> That''s fair enough,
>> but last time I did that I had two problems,
>>
>> 1. :joins doesn''t scope properly
>>   
> 
> a :joins isn''t enough, you must add the proper :conditions if
that''s
> what you mean (but I suspect I don''t get your meaning, I rarely
use
> scope and never used it with joins, so I may miss something).
> 
>> 2a. doing a join and not specifying the :select means that ambiguous 
>> columns, eg. "id" get overwritten in the returned models
>>   
> 
> Hum, happened to me too (funny that ActiveRecord doesn''t
automatically
> add the proper select when a joins exists, I still use 1.1.6 so it may
> have been added since then). :select => ''posts.*''
should be enough in
> the scope. In some cases you might have to deduplicate entries and use
> ''DISTINCT posts.*'', but not in this particular case.
> 
>> 2b. :select keys dont get scoped properly.
>>
>>   
> 
> ? I didn''t even try using them in a scope yet, care to elaborate?
> 
> Lionel

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

Rails - Jul 2007 - Trying tpo do complex finds with ActiveRecord

Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord

Re: Trying tpo do complex finds with ActiveRecord