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
--
Posted via http://www.ruby-forum.com/.
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 On 1/21/06, brian <brian@newobj.net> wrote:> > 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060121/5e24d5f1/attachment.html
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 -- Posted via http://www.ruby-forum.com/.
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 On 1/21/06, brian <brian@newobj.net> wrote:> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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
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 -- Posted via http://www.ruby-forum.com/.
>If you have criticisms of the schema, however, I ask that you pleasesuspend them I must read to the bottom of the post. Too early in the day.> I do not have the ability to refactorbummer Peter On 1/21/06, brian <brian@newobj.net> wrote:> 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
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.
brian wrote:> 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
>