Forum: Ruby on Rails n-way joins

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cf9c55418533b60bae67af0a01359e09?d=identicon&s=25 brian (Guest)
on 2006-01-21 09:23
Hi,

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:

foos
id - pk
name - unique

bars
id - pk
name - unique

bazs
id - pk
name - unique

frozs
id - pk
name - unique

then i have two separate join tables:

foos_bars_bazs - 3 way join
foo_id\
bar_id > primary key/unique
baz_id/
value

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

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
join.
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
ActiveRecord?

Thanks!
Brian
6661ef9d747db3af8896cd94959d717d?d=identicon&s=25 Paul Barry (Guest)
on 2006-01-21 14:23
(Received via mailing list)
Where are foreign keys?  For example, is it:

foos
id
name

bars
id
foo_id
name

or

foos
id
bar_id
name

bars
id
name

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

-movies
id (PK)
name (unique)

-actors
id (PK)
name (unique)

-magazines
id (PK)
name (unique)

-writers
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:

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

movies_magazines_writers
movie_id (FK)    |
magazine_id (FK) |
writer_id  (FK)  |
excerpt

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

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
(actor,movie).

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.

Thanks!
Brian
Eea3feaacbe44706164289d068d94828?d=identicon&s=25 Peter Michaux (Guest)
on 2006-01-21 18:19
(Received via mailing list)
Brian,

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?

actors_movies
articles_movies
actors_articles
articles_authors
articles_magazines

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
Eea3feaacbe44706164289d068d94828?d=identicon&s=25 Peter Michaux (Guest)
on 2006-01-21 18: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.

-Peter

* maybe publicities is not a word
Cf9c55418533b60bae67af0a01359e09?d=identicon&s=25 brian (Guest)
on 2006-01-21 18: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.

Thanks,
Brian
Eea3feaacbe44706164289d068d94828?d=identicon&s=25 Peter Michaux (Guest)
on 2006-01-21 18: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

bummer

Peter
17d01ff4326c26911481c7a77283b343?d=identicon&s=25 Eric Sloane (Guest)
on 2006-01-22 05:52
(Received via mailing list)
Brian,
I had a similar(ish) requirement a few weeks ago and followed a model
given by Chris Hall as follows;

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

(untested)

people_projects_roles (join table between people and projects and roles)
----------
person_id
role_id
project_id

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

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

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

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)
project.people

# all roles associated with "my project"
project.roles

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

# all projects assocated with "John Smith"
person.projects

# all roles assocated with "John Smith"
person.roles

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

# all projects with a "programmer" role
role.projects

# all people with a "progammer" role
role.people


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")
project.find(10)

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

project.people.push_with_attributes(john, :role_id => manager.id)
project.roles.push_with_attributes(manager, :person_id => john.id)
john.projects.push_with_attributes(project, :role_id => manager.id)
john.roles.push_with_attributes(manager, :project_id => project.id)
role.projects.push_with_attributes(project, :person_id => john.id)
role.people.push_with_attributes(john, :project_id => 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 <mailing@gp-net.nl> wrote:

     Eric,

     I don't comletely understand the definition of the entity Role.
Possibly
     usefull is to remember that when a project is closed or a person is
removed
     (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
there
     turn, have many roles within a project?

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

     Hope (wonder if) it helps.

     Regards,

     Gerard.


     On Monday 02 January 2006 02:10, Eric Sloane 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
various
     > perspectives. For example, I have a project view that presents
static
     > project data at the head of the screen with a set of tabs
containing
     > 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
of
     > 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.

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