How to Use Table Associations
Table associations are used by first instantiating a model class from with in one of your controllers. A model class at its very basic is only a few lines of code, but since it inherits from ActiveRecord? it already has tons of functionality without doing anything to the model class. Lets make an example to show how the table associations work. Lets say we have a project that belongs to our portfolio, has a project manager, has many milestones, and has and belongs to many categories. First lets make the tables to represent our data.
CREATE TABLE projects (
id int(11) NOT NULL auto_increment,
portfolio_id int(11) NOT NULL,
project_manager_id int(11) NOT NULL,
name varchar(40) default NULL,
created_at datetime default NULL,
PRIMARY KEY (id)
)
CREATE TABLE portfolios (
id int(11) NOT NULL auto_increment,
name varchar(40) default NULL,
PRIMARY KEY (id)
)
CREATE TABLE project_managers (
id int(11) NOT NULL auto_increment,
first_name varchar(40) default NULL,
last_name varchar(40) default NULL,
PRIMARY KEY (id)
)
CREATE TABLE milestones (
id int(11) NOT NULL auto_increment,
project_id int(11) NOT NULL,
title varchar(150) NOT NULL,
description text default NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories (
id int(11) NOT NULL auto_increment,
name varchar(150) NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories_projects (
category_id int(11) NOT NULL,
project_id int(11) NOT NULL,
PRIMARY KEY (id)
)
Now the corresponding Model classes for the above tables. Each of the <? ... ?> would be its own file, located in the trax/app/models folder. And don't worry you don't have to make these files either, you just use the generator script in the scripts folder. ( ./generator.php model ModelName? )
<?
class Project extends ActiveRecord {
public $belongs_to = "portfolio,project_manager";
public $has_many = "milestones";
public $has_and_belongs_to_many = "categories";
}
?>
<?
class Portfolio extends ActiveRecord {
public $has_many = "projects";
}
?>
<?
class ProjectManager extends ActiveRecord {
public $has_many = "projects";
}
?>
<?
class Milestone extends ActiveRecord {
public $has_one = "project";
}
?>
<?
class Category extends ActiveRecord {
public $has_and_belongs_to_many = "projects";
}
?>
Now lets have a controller called projects. This file would be located in trax/app/controllers. You don't have to make this file either, you just use the generator script in the scripts folder. ( ./generator.php controller ControllerName? index show ) this will also create the 2 view files index and show in trax/app/views/projects/index.phtml & show.phtml .
<?
class ProjectsController extends ApplicationController {
# this function would be the default action
# for this controller usually to display a list view
function index() {
# Instaniate a model class
$project = new Project();
# find_all() returns an array of objects
# representing rows in the database. Columns from the table
# will become class variables so I could do $model->field_name
$this->projects = $project->find_all();
}
# this function would be used to display a single project
function show() {
# Instaniate a model class
$project = new Project();
# find() returns an object
# representing one row in the database. Columns from the table
# will become class variables so I could do $model->field_name
$this->project = $project->find($_REQUEST['id']);
}
}
?>
Now my view file for index or the list view of all my projects. As you may or may not have noticed all variables you set as class variables in the controller get turned into regular variables in the view. So in the controller index method we set the results of find_all() to the class variable $this->projects which is an array of objects, but in the view we access it with just $projects, no $this. As well notice the cleaner syntax of not using curly brackets but the if(): endif; style syntax for mixing with html.
<h2>All Projects</h2>
<table>
<tr>
<td> </td>
<td>Project Name</td>
<td>Portfolio</td>
<td>Project Manager</td>
<td>Number of Milestones</td>
<td>Start Date</td>
</tr>
<? if(count($projects)): ?>
<? foreach($projects as $project): ?>
<tr>
<td><?= link_to("View", array(":action" => "show", ":id" => $project->id)) ?></td>
<td><?= $project->name ?></td>
<td><?= $project->portfolio->name ?></td>
<td><?= $project->project_manager->last_name ?>,
<?= $project->project_manager->first_name ?></td>
<td><?= count($project->milestones) ?></td>
<td><?= $project->created_at ?></td>
</tr>
<? endforeach; ?>
<? else: ?>
<tr><td align="center">no projects found.</td></tr>
<? endif; ?>
</table>
Now my view file for show or the single project view. Once again notice the cleaner syntax of not using curly brackets but the if(): endif; style syntax for mixing with html.
<h2> Project <?= $project->name ?></h2><br>
Portfolio: <?= $project->portfolio->name ?><br>
Project Manager: <?= $project->project_manager->first_name ?>,
<?= $project->project_manager->last_name ?><br>
<? if(count($projects->milestones)): ?>
Milestones:<br>
<? foreach($projects->milestones as $milestone): ?>
-<?= $milestone->name ?><br>
<? endforeach; ?>
<? else: ?>
no milestones found.
<? endif; ?>
<br>
<? if(count($projects->categories)): ?>
Currently in Categories:<br>
<? foreach($projects->categories as $category): ?>
-<?= $category->name ?><br>
<? endforeach; ?>
<? else: ?>
no categories found.
<? endif; ?>
<br>
Start Date: <?= $project->created_at ?><br>
Lastly to get to these view files in your browser you would goto:
( calls index method by default ) for the list view. http://www.domain.com/projects ( last part is the id 1,2,3 or whatever project id to load ). http://www.domain.com/projects/show/1
