Hi,
I am new to Ruby and Rails programming and am having difficulty with the
following scenario...
Consider the following model:
class Studio < ActiveRecord::Base
has_many :movies
has_and_belongs_to_many :actors
end
class Movie < ActiveRecord::Base
belongs_to :studio
has_and_belongs_to_many :actors
end
class Actor < ActiveRecord::Base
has_and_belongs_to_many :studios
has_and_belongs_to_many :movies
end
The underlying database structure is as follows:
studios
id: integer
movies
id: integer
studio_id: integer
actors
id: integer
actors_studios
actor_id: integer
studio_id: integer
actors_movies
actor_id: integer
movie_id: integer
(Note that in this case I am not using a movies_studios table)
The question I want to answer is, given an actor_id and studio_id, how
do I find all related movies? Or, stated differently, how do I find all
movies for a given studio/actor combination?
I have come up with several possible alternatives, but none of them
really satisfy-- or, at least, I think it should be able to be done
better. My possible choices are:
1. Use straight SQL -- disregarded as not the "Ruby Way"
2. Use find_by_sql -- ditto (although, perhaps less so)
3. Do the following:
Movie.find_by_studio_id(<studio id>).collect do |movie|
movie.actors.each.do |actor|
if (actor.id == <actor id>) then
<do work>
end
end
end
This is not satisfying because I have to loop through all actors
associated with each movie to see if I have a match. Seems like there
should be a better way...
4. Do the following:
studio = Studio.find(<studio id>)
actor = studio.actors.find(<actor id>)
actor.movies.each do |movie|
<do work>
end
This, again, is less than satisfying-- so many SQL calls...
So, is there a way to accomplish what I am looking for in a way that
generates minimal SQL? For example, if I were just going after the data
using direct SQL I would write the following query:
SELECT * FROM movies m
INNER JOIN actors_movies am ON m.id = am.movie_id
WHERE am.actor_id = <actor id>
AND m.studio = <studio id>
Do I need to create a model of actors_movies to do what I am trying to
do (which also is less than satisfying)? Seems like there has to be an
efficient way to get the data I am looking for without resulting in
multiple SQL queries, and without having to "filter" the result set
afterwards.
Thanks for any insights.
--
Posted via http://www.ruby-forum.com/.
I''m far from an "experienced" ruby developer, but the best way to do this would be using find_by_sql and put your SQL in... Otherwise you''re doing all kinds of queries, and then comparing the queries... It''s far more taxing than an SQL join. just my 2 cents. Eric Lacey wrote:> > Hi, > > I am new to Ruby and Rails programming and am having difficulty with the > following scenario... > > Consider the following model: > > class Studio < ActiveRecord::Base > has_many :movies > has_and_belongs_to_many :actors > end > > class Movie < ActiveRecord::Base > belongs_to :studio > has_and_belongs_to_many :actors > end > > class Actor < ActiveRecord::Base > has_and_belongs_to_many :studios > has_and_belongs_to_many :movies > end > > The underlying database structure is as follows: > > studios > id: integer > > movies > id: integer > studio_id: integer > > actors > id: integer > > actors_studios > actor_id: integer > studio_id: integer > > actors_movies > actor_id: integer > movie_id: integer > > (Note that in this case I am not using a movies_studios table) > > The question I want to answer is, given an actor_id and studio_id, how > do I find all related movies? Or, stated differently, how do I find all > movies for a given studio/actor combination? > > I have come up with several possible alternatives, but none of them > really satisfy-- or, at least, I think it should be able to be done > better. My possible choices are: > > 1. Use straight SQL -- disregarded as not the "Ruby Way" > 2. Use find_by_sql -- ditto (although, perhaps less so) > 3. Do the following: > > Movie.find_by_studio_id(<studio id>).collect do |movie| > movie.actors.each.do |actor| > if (actor.id == <actor id>) then > <do work> > end > end > end > > This is not satisfying because I have to loop through all actors > associated with each movie to see if I have a match. Seems like there > should be a better way... > > 4. Do the following: > > studio = Studio.find(<studio id>) > actor = studio.actors.find(<actor id>) > actor.movies.each do |movie| > <do work> > end > > This, again, is less than satisfying-- so many SQL calls... > > So, is there a way to accomplish what I am looking for in a way that > generates minimal SQL? For example, if I were just going after the data > using direct SQL I would write the following query: > > SELECT * FROM movies m > INNER JOIN actors_movies am ON m.id = am.movie_id > WHERE am.actor_id = <actor id> > AND m.studio = <studio id> > > Do I need to create a model of actors_movies to do what I am trying to > do (which also is less than satisfying)? Seems like there has to be an > efficient way to get the data I am looking for without resulting in > multiple SQL queries, and without having to "filter" the result set > afterwards. > > Thanks for any insights. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >-- View this message in context: http://www.nabble.com/Question-RE-Rails-associations-tf2116821.html#a5840271 Sent from the RubyOnRails Users forum at Nabble.com.
On 8/16/06, Eric Lacey <elacey@mathworks.com> wrote:> Hi, > > I am new to Ruby and Rails programming and am having difficulty with the > following scenario... > > Consider the following model: > > class Studio < ActiveRecord::Base > has_many :movies > has_and_belongs_to_many :actors > end > > class Movie < ActiveRecord::Base > belongs_to :studio > has_and_belongs_to_many :actors > end > > class Actor < ActiveRecord::Base > has_and_belongs_to_many :studios > has_and_belongs_to_many :movies > end >*snip*> > The question I want to answer is, given an actor_id and studio_id, how > do I find all related movies? Or, stated differently, how do I find all > movies for a given studio/actor combination? > > I have come up with several possible alternatives, but none of them > really satisfy-- or, at least, I think it should be able to be done > better. My possible choices are: > > 1. Use straight SQL -- disregarded as not the "Ruby Way"Ruby and Rails aren''t the same thing, you know. ;) But as you already use AR and have a model for movies, why not use it?> 2. Use find_by_sql -- ditto (although, perhaps less so)AR isn''t supposed to replace SQL, it''s just about making the common/simple things more convenient. Albeit flawed in some ways, SQL is extremely powerful, and simply the best tool available for working with relational db''s. Noone wants to take that away from you. Don''t call find_by_sql directly from your controller, though. Cleaner to wrap the call in a class method in your movies model.> 3. Do the following: > > Movie.find_by_studio_id(<studio id>).collect do |movie| > movie.actors.each.do |actor| > if (actor.id == <actor id>) then > <do work> > end > end > end > > This is not satisfying because I have to loop through all actors > associated with each movie to see if I have a match. Seems like there > should be a better way... > > 4. Do the following: > > studio = Studio.find(<studio id>) > actor = studio.actors.find(<actor id>) > actor.movies.each do |movie| > <do work> > end > > This, again, is less than satisfying-- so many SQL calls...If the people you work with have no grasp of SQL, you _may_ get acceptable performance from something like this: movies = my_studio.movies & my_actor.movies (i.e. doing a set intersection on the two associations (Ruby comparing two result sets/arrays of movies)). find_by_sql is obviously better, but depending on your data / user mass, you may get away with something like this for now. If you want the ''rails'' way, I''d say that find_by_sql is it. Isak
Couldn''t you do Movie.find_by_actor_and_studio?> Consider the following model: > > class Studio < ActiveRecord::Base > has_many :movies > has_and_belongs_to_many :actors > end > > class Movie < ActiveRecord::Base > belongs_to :studio > has_and_belongs_to_many :actors > end > > class Actor < ActiveRecord::Base > has_and_belongs_to_many :studios > has_and_belongs_to_many :movies > end >*snip*> > The question I want to answer is, given an actor_idand studio_id, how> do I find all related movies? Or, stateddifferently, how do I find all> movies for a given studio/actor combination?__________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com