# Can this be done without resorting to sql?

**URL:** <https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009>\
**Category:** Rails\
**Created:** [January 3, 2007, 12:47am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009 "2007-01-03T00:47:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![giuseb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e495f1/32.png) [@giuseb](https://www.ruby-forum.com/u/giuseb)\
**Post date:** [January 3, 2007, 12:47am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009/1 "2007-01-03T00:47:45Z")

</div>

Hello and happy new year 🙂

My application has users, memberships, projects, and entries.  
Briefly, a project has many entries and users are granted access to such  
entries based on a project membership (a full-blown model, not a simple  
join table).

class User \< ActiveRecord::Base  
has\_many :memberships  
has\_many :projects, :through =\> :memberships  
…

class Membership \< ActiveRecord::Base  
belongs\_to :user  
belongs\_to :project  
…

class Project \< ActiveRecord::Base  
has\_many :memberships  
has\_many :users, :through =\> :memberships  
has\_and\_belongs\_to\_many :entries  
…

class Entry \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :projects  
belongs\_to :content, :polymorphic =\> true  
…

Now I would like, for example, to get the title of all entries to which  
the current user has access to, from all projects she may be part of.  
Although each entry can belong to any number of projects, and users can  
be members of any number of projects, each entry should be retrieved  
only once.  
I believe that this can be done in SQL with something like (assuming  
that the current user’s id is 1):

SELECT DISTINCT title  
FROM entries  
INNER JOIN entries\_projects ON entries.id=entries\_projects.entry\_id  
WHERE project\_id IN  
(SELECT project\_id FROM memberships WHERE user\_id=1);

But my question is whether the same can be accomplished in idiomatic  
rails style through the AR facilities, something along the lines of  
@current\_user.projects.entries (which obviously won’t work)

Thanks in advance for any advice.  
Cheers,  
Giuseppe

---

<div class="post-metadata">

**Author:** ![giuseb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e495f1/32.png) [@giuseb](https://www.ruby-forum.com/u/giuseb)\
**Post date:** [January 3, 2007, 2:06pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009/2 "2007-01-03T14:06:41Z")

</div>

Hi again… still hoping that someone might have some insight.

As a newbie, I would also appreciate any feedback regarding the post  
itself, as in “it’s too vague, too obscure, too specific, out-of-place,  
etc.”

I’ll gladly take any well-deserved hammering! 🙂

Ciao,  
Giuseppe

---

<div class="post-metadata">

**Author:** ![giuseb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e495f1/32.png) [@giuseb](https://www.ruby-forum.com/u/giuseb)\
**Post date:** [January 4, 2007, 12:45am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009/3 "2007-01-04T00:45:12Z")

</div>

Hi Guiseppe,

I found your post well written. I think posts that are more difficult  
to respond to tend to languish. I couldn’t think of a good Rails way to  
do this other than the following. You could add your own find\_titles  
method to Entry like this:

class Entry \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :projects  
belongs\_to :content, :polymorphic =\> true

def self.find\_titles(user\_id)  
entries = find(:all,  
:select =\> “distinct(title)”,  
:joins =\> “inner join entries\_projects ON  
entries.id=entries\_projects.entry\_id”,  
:conditions =\> [" project\_id IN(SELECT project\_id FROM  
memberships WHERE user\_id= ?)", user\_id])  
entries.map {|e| e.title}  
end  
end

And then you could call it like this:

Entry.find\_titles(1)

It makes your code cleaner. Better yet, you could put this code in a  
new instance method in User called “titles” and then just call  
@user.titles although in the titles method you would want to say  
Entry.find\_titles(self.id).

-Paul

---

<div class="post-metadata">

**Author:** ![giuseb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e495f1/32.png) [@giuseb](https://www.ruby-forum.com/u/giuseb)\
**Post date:** [January 5, 2007, 1:00am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009/4 "2007-01-05T01:00:44Z")

</div>

Hi Paul,  
many thanks for the feedback.  
I am still trying to get a feel for the language and the framework.  
In this case, the way ‘find’ is used looks a lot like sql broken down in  
several pieces, so I wonder what is the advantage over using straight  
find\_by\_sql.

Meanwhile, preliminary testing shows that the following might just work,  
although some more testing is needed.

@entries = @current\_user.projects.map{|g| g.entries}.flatten.uniq

The flattening trick was inspired by:  
[http://www.rubyonrailsblog.com/articles/2006/10/04/ruby-on-rails-cheat-sheet-collectors-edition](http://www.rubyonrailsblog.com/articles/2006/10/04/ruby-on-rails-cheat-sheet-collectors-edition)

Putting together this rather obscure line of code was fun, even though I  
still need to develop a _pragmatic_ sense of whether it was the right  
kind of effort in the first place.

Buona notte,  
Giuseppe

---

<div class="post-metadata">

**Author:** ![giuseb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e495f1/32.png) [@giuseb](https://www.ruby-forum.com/u/giuseb)\
**Post date:** [January 11, 2007, 4:05am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-resorting-to-sql/81009/5 "2007-01-11T04:05:29Z")

</div>

On 1/4/07, Giuseppe B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I am still trying to get a feel for the language and the framework.  
> In this case, the way ‘find’ is used looks a lot like sql broken down in  
> several pieces, so I wonder what is the advantage over using straight  
> find\_by\_sql.

One advantage would be that if you use ActiveRecord’s with\_scope  
method (which facilitates many cool DB tricks), and your find is made  
within its block, the scope will automatically be applied to it. I  
don’t think that this happens with find\_by\_sql, looking at the  
with\_scope code.

There might be other benefits.

> kind of effort in the first place.  
> If you know @current\_user.projects won’t return “a lot” of results,  
> then this is fine. If this line slows down your page though, you’ll  
> know it’s because you’re making Ruby do too much of the database  
> server’s work and moving back to the SQL solution will probably help.

> Buona notte,  
> Giuseppe

Sincerely,

Tom L.  
[http://AllTom.com/](http://AllTom.com/)  
[http://GadgetLife.org/](http://GadgetLife.org/)