Using ActiveRecord in the Zend Framework

Intro
I started to brush up on my Ruby on Rails skills over the weekend by building a small web site and looking into the Active Record layer of the framework and wondered if the Zend Framework contained something similar. After looking through the documentation I found the answer, Its offered.

What is Active Record?
In short I will use the, now standard, definition taken from Martin Fowler’s book, Patterns of Enterprise Application Architecture,

“An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.”

What that basically means is, a class now represents a table. A row in the table represents an object and finally a column in the row represents an attribute in the object. If we had a table called, “Dogs”, we would have a class that represents the table, called “Dog”. Let’s get to the examples.

Getting Started
To start, let’s create a table structure. For this example we will create a database called “ar_example” and create a relationships between tables, “books” and “authors”. Both the ERD and the DDL for creating the database/tables is listed below.

SQL:
CREATE DATABASE ar_example;
CREATE TABLE books (id int(11) AUTO_INCREMENT PRIMARY KEY, title varchar(50) NOT NULL, authors_id int(11) NOT NULL REFERENCES authors.id);
CREATE TABLE authors (id int(11) AUTO_INCREMENT PRIMARY KEY, full_name varchar(50) NOT NULL);

The above DDL will create a database along with two tables. The, “books”, table containing a unique id, the title of the book, and the id of the author. The “authors”, table contains a unique id, and the full name of the author. For this example our authors have only one book to their name.

Turning the table structure above into usable PHP5 Data Abstraction Object models (DAO) we create the two files below.

Book.php
<?php
require_once “Zend/Db/Table/Abstract.php”;
class Book extends Zend_Db_Table_Abstract {

protected $_name = “books”;
protected $_primary = “id”;

}
?>

Author.php
<?php
require_once “Zend/Db/Table/Abstract.php”;
class Author extends Zend_Db_Table_Abstract {

protected $_name = “authors”;
protected $_primary = “id”;

}
?>

We have just created 2 models and saved them into our models directory. Each model above extends the Zend_Db_Table_Abstract class, uses the $_name and the $_primary attributes to set the table name to reference and the primary key of the table. By extending the Zend_Db_Table_Abstract class we are allowed to use a handful of pre-built goodies such as locating rows, updating, and deleting records without writing one line of SQL.

What the Zend_Db_Table_Abstract class has to offer.
Zend_Db_Table_Abstract, as previously mentioned comes with a load of useful methods we’ve all used one time or another. Let’s say we want to find a author by his/her specific id, update, or even delete an entry, no more writing SQL code for that.

Selecting A Row
<?php

//Initialize your DB connection here
$db = Zend_Db::factory(‘adapter type’, $options);

//Instantiate objects
require_once “Authors/Author.php”;
$Author = new Author(array(‘db’ => $db));
$authorRow = $Author->find(1);

?>

Inserting A Record
<?php

//Initialize your DB connection here
$db = Zend_Db::factory(‘adapter type’, $options);

require_once “Authors/Author.php”;
$Author = new Author(array(‘db’ => $db));
$data = array(‘full_name’ => ‘Snoopy Padilla’); //Add more key’s into this array for each column in your table.
$Author->insert($data);

?>

Updating A Record
<?php

//Initialize your DB connection here
$db = Zend_Db::factory(‘adapter type’, $options);

$Author = new Author(array(‘db’ => $db));
$authorRow = $Author->fetchRow(“id=14”);
$authorRow->full_name = “Snoopy Updated”;
$authorRow->save();

?>

Deleting a Record
<?php

//Initialize your DB connection here
$db = Zend_Db::factory(‘adapter type’, $options);

$Author = new Author(array(‘db’ => $db));
$authorRow = $Author->fetchRow(“id=2”);
$authorRow->delete();

?>

Notice that in the above three examples no additional code or sql was added to either our php files or our models, it was all built in, leaving you plenty of time on your hands to go have a cup of fresh coffee.

How to map a One-To-One Relationship using the Zend Framework
Now comes the mapping. What’s mapping? Mapping is a way to tell PHP which models have relationships to one another. This in turn will informs PHP which tables relate to one another. In our example, a book can only have one author so the model will have a reference from the Author Model to the Book model.

Book.php
<?php
require_once “Zend/Db/Table/Abstract.php”;
class Book extends Zend_Db_Table_Abstract {

protected $_name = “books”;
protected $_primary = “id”;
protected $_referenceMap = array(“Author” => array(“columns” => array(“author_id”),
“refTableClass” => “Author”,
“refColumns” => array(“id”)));

}
?>

Author.php
<?php
require_once “Zend/Db/Table/Abstract.php”;
class Author extends Zend_Db_Table_Abstract {

protected $_name = “authors”;
protected $_primary = “id”;
protected $_dependentTables = array(“Book”);

}
?>

We are telling the PHP 5 model, Book, that the Book model references the Author model. The foreign key in the Book model is ‘author_id’ specified in the ‘columns’ array and the referencing table, ‘Author’, is specified by the ‘refTableClass’ element. Notice the name of the table, we are using the name of the model, ‘Author’, and not the actual name of the table, ‘Authors’. Continuing, the column that the ‘Book’ table’s foreign key references is ‘id’ inside the Author table. Easy huh.

Now lets use the above mapping to fetch all of Snoopy’s books he’s published.

Example.php
<?php

require_once “Authors/Author.php”;
require_once “Books/Book.php”;

//Initialize your DB connection here
$db = Zend_Db::factory(‘adapter type’, $options);

$Author = new Author(array(‘db’ => $db));
$authorRow = $Author->find(2);
$author = $authorRow->current();
$books = $author->findDependentRowset(‘Book’);

?>

How to map a Many-To-Many Relationship using the Zend Framework
Extending our example we are going to add in a many-to-many relationship. We will create one more table a, ‘Authors_Books’, table. The Authors_Books table will map all the authors to all the books. In this example, books can have many authors and authors can have many books to their name.

SQL:
CREATE TABLE authors_books (authors_id int(11) NOT NULL REFERENCES authors.id , books_id int(11)     NOT NULL REFERENCES books.id );

Creating our model for the new table will be straight forward.

AuthorsBooks.php:
<?php
require_once “Zend/Db/Table/Abstract.php”;
class AuthorsBooks extends Zend_Db_Table_Abstract {

protected $_name = “authors_books”;

}
?>

Adding in mapping to the new models will take only seconds. Our new AuthorsBooks model is shown below.

AuthorsBooks.php
<?php
require_once “Zend/Db/Table/Abstract.php”;

class AuthorBooks extends Zend_Db_Table_Abstract {

protected $_name = “authors_books”;
protected $_referenceMap = array(
“Book”   => array(“columns” => array(“books_id”), “refTableClass” => “Book”, “refColumns” => array(“id”)),
“Author” => array(“columns” => array(“authors_id”), “refTableClass” => “Author”, “refColumns” => array(“id”))
);

}
?>

Putting the above mapping into use we can get all the authors for a particular book without any complicated INNER JOIN SQL statements. Here’s how its done.

Selecting all the authors of a book.
<?php

        $Author = new Author(array(“db” => $db));
$authorRowSet = $Author->find(3);
$author = $authorRowSet->current();

$books = $author->findManyToManyRowset(‘Book’, ‘AuthorBooks’);

?>

Pros
What are the gems of these method? NO MORE SQL, which means more time worrying about business logic and design. It abstracts the database from the developer to the point that the developer no longer has to be an expert at their given database engine. Did I also mention no more SQL calls? This might seem like there are hardly any Pro points but, if you’ve done web development work for some time you see how redundant applications can be at times and how you desperetely need a way to automate the dirty work.

Also, the article did not cover all the small addition that the Framework allows you to add to support your database. For example there are magic methods which allow you find items from a particular table just by calling find<Table Name> or finding items with conditions using find<Table Name>By<Condition>. The framework also allows you to set up your own cascading calls when updating or deleting triggers are fired. More info on that read this.

Cons
The largest mark against the Active Record implementation from Zend is that there are no cascading inserts. I cant get a Row from a table such as Dog and add in a new dog and associate it with a breed. The framework also doesnt allow you to implement polymorphic association, which i think is OK since you can go around that very easily by creating another model. The next mark against it is not the framework itself but the idea of Active Record within a framework. Since the Active Record is built into the framework complicated associations will lock you into using the framework. You will not be able to move to another framework as easy as implementing your own data access layer.

Conclusion.
Overall Zend has come along way from the PHP 4 mess that it once was. Zend has introduced a nice Active Record layer that, for some (including myself) contains unnecessary methods such as the Select Object, while adding nice features such as the magic methods for development efficientcy. It still has a long way to go but it has the building blocks that make for a good fundamental framework.

References:
Zend Framework
PHP
Zend_Db_Table_Abstract