Forum: Ruby on Rails n-way joins

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
brian (Guest)
on 2006-01-21 10:23

I'm somewhat of a Rails newbie and am trying to understand how to
formulate n-way (3 or 4 way) joins in Rails (where the join tables
contain extra data as well.)

Let me give you my basic entities:

id - pk
name - unique

id - pk
name - unique

id - pk
name - unique

id - pk
name - unique

then i have two separate join tables:

foos_bars_bazs - 3 way join
bar_id > primary key/unique

foos_bars_bazs_frozs - 4 way join
foo_id  \
bar_id   \ primary key/unique
baz_id  /

Now, my use cases for access are:

1. Given a foo, find all { bar, baz, value } associated with it (i'd
really like to get back bars and bazs, not just their ids)
2. Given a froz and froo, find all { bar, baz, values } associated with
them (again, i'd like to get bar and baz back as objects, not id's }
3. Given a foo, insert a new {foo,bar,baz,value} tuple into the 3 way
4. Given a froz and a food, insert a new {froz,foo,bar,baz,value} tuple
into the 4 way join.

I'll omit my attempts to model this so far, as they've failed. Can
someone give me a hand in understanding how to model this using

Paul B. (Guest)
on 2006-01-21 15:23
(Received via mailing list)
Where are foreign keys?  For example, is it:






Also, this might be easier to understand if you use the real names of
objects you are talking about
brian (Guest)
on 2006-01-21 18:59
Hmm, the only FK's are in the join table. The other tables are all
domain tables.

id (PK)
name (unique)

id (PK)
name (unique)

id (PK)
name (unique)

id (PK)
name (unique)

So suppose we're trying to track media coverage of movies, artists, and
movies+artists. We'll always have a magazine + writer, but articles can
either be about movies, artists, or artists in the context of a movie.
So our tables would be:

movie_id (FK)    |
actor_id (FK)    | PK
magazine_id (FK) |
writer_id  (FK)  |

movie_id (FK)    |
magazine_id (FK) |
writer_id  (FK)  |

actor_id (FK)    | PK
magazine_id (FK) |
writer_id  (FK)  |

So my retrieval use cases are:

1. Given an actor, find me all excerpts and then display a list of
excerpts with the magazine name and writer name displayed alongside.

2. Given a movie, find me all excerpts and then display a list of
excerpts with the magazine name and writer name displayed alongside.

3. Given an actor in the context of a movie, find me all excerpts and
then display a list of excerpts with the magazine name and writer name
displayed alongside.

In terms of update/create/delete, it will also be oriented towards the
actors, movies, and (actor,movie)s. The user can perform creation,
updates, and deletion of magazine/writer/excerpts ON an actor, movie, or

I appreciate any help you can offer with how to model this with objects.
If you have criticisms of the schema, however, I ask that you please
suspend them, as I'm really just trying to learn about the ORM aspects
or Rails.

Peter M. (Guest)
on 2006-01-21 19:19
(Received via mailing list)

I'm no expert about db normalization. I'm not sure exactly what you
want to do, of course. Given my qualifications on your problem, I
don't think you want tables like movies_actors_magazines_writers *and*
movies_magazines_writers because all the information in the latter is
contained by the former.

Don't you really want to relate movies and actors to articles which
have a magazine and a author?

Wouldn't these five two-way join tables would be the most flexible?


This way you can have muliple authors per article easily.

Also if you want to add director to the mix you only need to add one
table called directors_movies. (you don't need many complicated tables
like movies_directors_actors_magazines_authors)

Hope this helps.

Peter M. (Guest)
on 2006-01-21 19:29
(Received via mailing list)
In the rails book DHH talks about join tables that want to be models
(page 241). This discussion relates to the case where the join model
has extra data. This also prepares you for the has_many :through
feature that is upcomming in rails 1.1. This is instead of using the
has_and_belongs_to_many feature which has shortcomings.

Naming the join tables so that they seam like models is something I'm
not good at yet. You could try things like

actors_movies -> performances
articles_movies -> movie_publicities*
actors_articles -> actor_publicities*
articles_authors -> authorship
articles_magazines -> magazine_articles or article_publications

Another thought. Only the articles_movies and actors_articles join
tables need to have the excerpts you listed before.

I still could be barking up the wrong tree for you.


* maybe publicities is not a word
brian (Guest)
on 2006-01-21 19:39
Hi Peter,

Thanks for the reply. The tables are really just examples. I have some
legacy tables with an essentially identical schema that I do not have
the ability to refactor and I'm wondering if/how they could be modeled
in Rails objects.

Peter M. (Guest)
on 2006-01-21 19:59
(Received via mailing list)
>If you have criticisms of the schema, however, I ask that you please
suspend them

I must read to the bottom of the post. Too early in the day.

>  I do not have the ability to refactor


Eric S. (Guest)
on 2006-01-22 06:52
(Received via mailing list)
I had a similar(ish) requirement a few weeks ago and followed a model
given by Chris H. as follows;

I might suggest the following (and I am making the asusmption that the
relationships between projects, people and roles is unlimited


people_projects_roles (join table between people and projects and roles)

class Project < ActiveRecord::Base
   has_and_belongs_to_many :people, :join_table =>
   has_and_belongs_to_many :roles, :join_table =>

class Person < ActiveRecord::Base
   has_and_belongs_to_many :projects, :join_table =>
   has_and_belongs_to_many :roles, :join_table =>

class Role < ActiveRecord::Base
   has_and_belongs_to_many :people, :join_table =>
   has_and_belongs_to_many :projects, :join_table =>

now, this allows you to do things such as

project = Project.find(1) # "my project"

# all people who are associated with "my project" (any role)

# all roles associated with "my project"

person = Person.find(1) # "John S."

# all projects assocated with "John S."

# all roles assocated with "John S."

role = Role.find(1) # "programmer"

# all projects with a "programmer" role

# all people with a "progammer" role

now say you want to add John as a "manager" (id = 2) role to Project 10

john = Person.find_by_name("John")
manager = Role.find_by_name("Manager")

with this information, you could do it several different
ways...depending on the situation

project.people.push_with_attributes(john, :role_id =>
project.roles.push_with_attributes(manager, :person_id =>
john.projects.push_with_attributes(project, :role_id =>
john.roles.push_with_attributes(manager, :project_id =>
role.projects.push_with_attributes(project, :person_id =>
role.people.push_with_attributes(john, :project_id =>

each of these accomplish the same thing, they add John as a Manager to
Project 10

now, as far as organisation/projects/roles go, that sounds strange (not
being critical)...can an organisation have the same roles as a person?
i would assume that organisations have different roles than people so
you will want to setup a separate "org roles" table to manage those.

can an organisation be involved in many projects and can a project have
many organisations (stakeholders)?  if so, then i would setup another
join table between organisations/projects/org roles and follow the same
idea as above

now, one thing i would be concerned about is corss referencing (not sure
the proper term).  you have people associated with projects,
organisations assocated with projects and people associated with
organisations...this can get messy when you want to start limiting who
can do based upon their other associations (ie, given a
project/organisation association, can only people associated with the
same organisation be assocated with that project?)

hope this helps.

On 1/3/06, Gerard <removed_email_address@domain.invalid> wrote:


     I don't comletely understand the definition of the entity Role.
     usefull is to remember that when a project is closed or a person is
     (started working for another company) that the data is stil
accessible. To
     simplify this. When a qoute or invoice is generated there's always
a copy
     used of the contacts data. Because when that person doesn't work
for a
     company anymore and is removed I would still want to be able to
view the
     quote/invoice/whatever without getting 'nil' object errors.

     Furthermore a look on what historical info you might save could
possibly help
     as well on the relationships you want to maintain.

     Looks like the 2nd one seems good (couldn't say why though). But
how does a
     project have many roles. Doesn't a project have many people who, in
     turn, have many roles within a project?

     I'm on the virge of diving into a similar mather on my internal
     appliation and I must admit this fun stuff to chew on .. :-)

     Hope (wonder if) it helps.



     On Monday 02 January 2006 02:10, Eric S. tried to type
something like:
     > Hi,
     > I'm trying to figure the most efficient way to model the
following. I
     > can think of at least two ways to relate the tables but from a
     > client/server perspective! I'm wondering how to best (and
     > elegantly)relate them from an AR perspective.
     > A project has many people,
     > A person can work on many projects at any time,
     > A project has many roles,
     > A role is performed by a person,
     > A person may perform multiple roles,
     > An organisation has many people,
     > An organisation is a stakeholder (God, I hate that word - makes
me feel
     > like Dracula surrounded!)in one or more projects,
     > A stakeholder has many roles within a project.
     > So one way I have
     > Projects HABTM Roles
     > Roles HABTM People
     > Organisation Has_Many People
     > An Organisation Belongs_to a Stakeholder
     > A Stakeholder HABTM Projects
     > A Stakeholder Has_Many Roles
     > Or
     > Projects HABTM People,
     > A Project has_many roles,
     > People HABTM Roles,
     > An Organisation has_many People,
     > An Organisation is a Stakeholder in a Project,
     > A Stakeholder has_many Roles in a Project.
     > I guess the outcome I'm after is a way to view this data from
     > perspectives. For example, I have a project view that presents
     > project data at the head of the screen with a set of tabs
     > partials with forms for editing stuff like e.g. People
     > Acting_For(Stakeholder), Acting_As (Role). Other perspectives
would be
     > like seeing which organisations are doing what within any number
     > projects - that sorta thing.
     > Whatya think?
     > Eric.

What Chris suggested certainly provided a lot of information in one hit
- possibly way too much. There is some further need to eliminate
join_table records that do not belong to the current job record cos when
you query this you get all records that fit any of the three criteria.

Hope that helps
This topic is locked and can not be replied to.