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
âŚ
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
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 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:
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).
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.
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.
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.
