Question RE Rails associations

Rails
1

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().collect do |movie|
    movie.actors.each.do |actor|
    if (actor.id == ) then

    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()
    actor = studio.actors.find()
    actor.movies.each do |movie|

    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 =
AND m.studio =

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.

2

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 L. wrote:

has_and_belongs_to_many :actors
end
actors
(Note that in this case I am not using a movies_studios table)
2. Use find_by_sql – ditto (although, perhaps less so)
This is not satisfying because I have to loop through all actors

Rails mailing list
[email protected]
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.

3

On 8/16/06, Eric L. [email protected] wrote:

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. :wink: But as you already
use AR and have a model for movies, why not use it?

  1. 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.

This is not satisfying because I have to loop through all actors

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