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.
Hi –
On Sat, 7 Jul 2007, Chinmay K. 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
–
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
–
–
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.
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 K. 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
–
–
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.
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 L. 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-with-ActiveRecord-tf4041039.html#a11514626
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Matthew R. 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
That’s fair enough,
but last time I did that I had two problems,
- :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
Matthew R. wrote:
That’s fair enough,
but last time I did that I had two problems,
- :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
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 B. wrote:
Matthew R. wrote:
That’s fair enough,
but last time I did that I had two problems,
- :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