How to join database tables together?

Rails
1

I’m just getting started with RoR and am not sure how to do table JOINs.
I’ve looked through the code used on sites in the SVN for RailsDay and
many of them seem to use a very small amount of code and I can’t figure
out how to do this from looking at them.

Below are my database tables. What I need to do is create a list that
looks like this for each team:

Team 1

Location
Somewhere

Leader
John D.
Mary Jane

Members
Steve Williams
Jack Black
etc.

CREATE TABLE team_leaders (
team_id int(4) NOT NULL default ‘0’,
people_id int(4) NOT NULL default ‘0’,
PRIMARY KEY (team_id,people_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE team_members (
team_id int(4) NOT NULL default ‘0’,
people_id int(4) NOT NULL default ‘0’,
PRIMARY KEY (team_id,people_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE teams (
id int(3) NOT NULL default ‘0’,
name varchar(100) NOT NULL default ‘’,
location varchar(100) NOT NULL default ‘’,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE people (
id int(5) NOT NULL default ‘0’,
name varchar(100) NOT NULL default ‘’,
email varchar(100) NOT NULL default ‘’,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Any help would be greatly appreciated!

2

Hi, here’s a snippet from section 17.5 of ‘Agile Web D. with
Rails
2ed’.

:joins
The :joins parameter to the finder method lets you specify a list of
additional
tables to be joined to the default table. This parameter is inserted
into
the
SQL immediately after the name of the model’s table and before any
conditions
specified by the first parameter. The join syntax is database-specific.
The

CRUDâ??CREATE, READ, UPDATE, DELETE 286
following code returns a list of all line items for the book called
Programming
Ruby.

LineItem.find(:all,
:conditions => “pr.title = ‘Programming Ruby’”,
:joins => “as li inner join products as pr on li.product_id = pr.id”)

As we’ll see in Chapter 18, Active Record Part II
Relationships Between Tables, on page 306, you probably won’t use the
:joins
parameter of find( ) very muchâ??Active Record handles most of the common
intertable joins for you.

Good luck,

-Conrad

3

John S. wrote:

I’m just getting started with RoR and am not sure how to do table JOINs.
I’ve looked through the code used on sites in the SVN for RailsDay and
many of them seem to use a very small amount of code and I can’t figure
out how to do this from looking at them.

Below are my database tables. What I need to do is create a list that
looks like this for each team:

Team 1

Location
Somewhere

Leader
John D.
Mary Jane

Members
Steve Williams
Jack Black
etc.

Any help would be greatly appreciated!

ActiveRecord associations handle all the common cases for you.
team.members returns a list of TeamMember model objects joined on the
team_id field. Go read up on ActiveRecord and associations.

–
Josh S.
http://blog.hasmanythrough.com