Forum: Ruby on Rails Trying tpo do complex finds with ActiveRecord

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Db20c311ea335cbfa3ccc9e6773e3580?d=identicon&s=25 Chinmay Kulkarni (Guest)
on 2007-07-07 18:44
(Received via mailing list)
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-...
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
1fba4539b6cafe2e60a2916fa184fc2f?d=identicon&s=25 unknown (Guest)
on 2007-07-07 19:00
(Received via mailing list)
Hi --
On Sat, 7 Jul 2007, Chinmay Kulkarni wrote:

> So to find a particular friend's posts, I do posts =
> 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)
Db20c311ea335cbfa3ccc9e6773e3580?d=identicon&s=25 Chinmay Kulkarni (Guest)
on 2007-07-07 19:15
(Received via mailing list)
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-...
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
E60b2dc57668b5662ce3f07781e41710?d=identicon&s=25 Matthew Rudy Jacobs (matthewrudy)
on 2007-07-09 00:01
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-...
> Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Db20c311ea335cbfa3ccc9e6773e3580?d=identicon&s=25 Chinmay Kulkarni (Guest)
on 2007-07-10 06:14
(Received via mailing list)
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:
>     end
>         if ids.empty?
> end
>> recent
>>> You can actually use find on the collection:
>>>
>
> >
>
>

--
View this message in context:
http://www.nabble.com/Trying-tpo-do-complex-finds-...
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Bef7ff8a0537495a1876ffebdc9f8e51?d=identicon&s=25 Lionel Bouton (Guest)
on 2007-07-10 12:14
(Received via mailing list)
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
E60b2dc57668b5662ce3f07781e41710?d=identicon&s=25 Matthew Rudy Jacobs (matthewrudy)
on 2007-07-10 13:31
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
Bef7ff8a0537495a1876ffebdc9f8e51?d=identicon&s=25 Lionel Bouton (Guest)
on 2007-07-10 14:51
(Received via mailing list)
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
E60b2dc57668b5662ce3f07781e41710?d=identicon&s=25 Matthew Rudy Jacobs (matthewrudy)
on 2007-07-10 15:24
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
This topic is locked and can not be replied to.