# Trying tpo do complex finds with ActiveRecord

**URL:** <https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827>\
**Category:** Rails\
**Created:** [July 7, 2007, 6:44pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827 "2007-07-07T18:44:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 7, 2007, 6:44pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/1 "2007-07-07T18:44:54Z")

</div>

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](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](http://Nabble.com).

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 7, 2007, 7:00pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/2 "2007-07-07T19:00:55Z")

</div>

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

–

- Books:  
RAILS ROUTING (new! [http://www.awprofessional.com/title/0321509242](http://www.awprofessional.com/title/0321509242))  
RUBY FOR RAILS ([Ruby for Rails](http://www.manning.com/black))
- Ruby/Rails training  
& consulting: Ruby Power and Light, LLC ([http://www.rubypal.com](http://www.rubypal.com))

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 7, 2007, 7:15pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/3 "2007-07-07T19:15:58Z")

</div>

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](http://www.awprofessional.com/title/0321509242))  
> RUBY FOR RAILS ([Ruby for Rails](http://www.manning.com/black))
> - Ruby/Rails training  
> & consulting: Ruby Power and Light, LLC ([http://www.rubypal.com](http://www.rubypal.com))
> 
> >

–  
View this message in context:  
[http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480356](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](http://Nabble.com).

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 9, 2007, 12:01am UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/4 "2007-07-09T00:01:51Z")

</div>

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
> > 
> > –
> > 
> > - Books:  
> > RAILS ROUTING (new! [http://www.awprofessional.com/title/0321509242](http://www.awprofessional.com/title/0321509242))  
> > RUBY FOR RAILS ([Ruby for Rails](http://www.manning.com/black))
> > - Ruby/Rails training  
> > & consulting: Ruby Power and Light, LLC ([http://www.rubypal.com](http://www.rubypal.com))
> > 
> > >
> 
> –  
> View this message in context:  
> [http://www.nabble.com/Trying-tpo-do-complex-finds-with-ActiveRecord-tf4041039.html#a11480356](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](http://Nabble.com).

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 10, 2007, 6:14am UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/5 "2007-07-10T06:14:31Z")

</div>

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](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](http://Nabble.com).

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 10, 2007, 12:14pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/6 "2007-07-10T12:14:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 10, 2007, 1:31pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/7 "2007-07-10T13:31:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 10, 2007, 2:51pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/8 "2007-07-10T14:51:32Z")

</div>

Matthew R. 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

---

<div class="post-metadata">

**Author:** ![Chinmay\_K](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/77aa72/32.png) [@Chinmay\_K](https://www.ruby-forum.com/u/Chinmay_K)\
**Post date:** [July 10, 2007, 3:24pm UTC](https://www.ruby-forum.com/t/trying-tpo-do-complex-finds-with-activerecord/108827/9 "2007-07-10T15:24:36Z")

</div>

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,
> > 
> > 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