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

Hi --

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

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:

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:

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

Matthew Rudy wrote:

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

1. :joins doesn't scope properly
  
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