epwzf20

Recognizing this, the Zend developers integrated several powerful features capable of dealing with related data. Notably, these features allow you to transparently treat a related row as another object attribute. Of course, these relations are only available when a normalized database is used, meaning you'll need to properly structure your database schema using primary and foreign keys. To demonstrate how Zend_Db can manage relations, start by altering the games table to include a foreign key which will reference a row within a table containing information about available gaming platforms:

mysql>ALTER TABLE games ADD COLUMN platform_id TINYINT UNSIGNED ->NOT NULL AFTER id;

Next create the platforms table:

CREATE TABLE platforms (

id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL,

abbreviation VARCHAR(10) NOT NULL

);

Finally, create the Platform model, which we'll use to access the newly created platforms table:

%>zf create db-table Platform

Once created, update the class found in Platform.php in the same manner we did with the Game model at the beginning of this chapter.

With the schema updated and necessary models in place you'll next need to configure the relationships within your models. The games table is dependent upon the platforms table, so let's start by defining the Game model's subservient role within the Platform model. Update the Platform model to include the protected attribute presented on Line 07 of the following listing:

01 class Application_Model_DbTable_Platform extends Zend_Db_Table_Abstract 02 { 03

04 protected $_name = 'platforms';

05 protected $_primary = 'id';

06

07 protected $_dependentTables = array('Application_Model_DbTable_Game'); 08 }

Line 07 defines the relationship, informing Zend_Db of the existence of a column within the Game model which stores a foreign key pointing to a row managed by the Platform model. If a model happens to be a parent for more than one other model, for instance the Game model is a parent to

the Rank and the AccountGame models, you would revise the $_dependentTables attribute to look like this:

protected $_dependentTables = array('Application_Model_DbTable_Rank', 'Application_Model_DbTab

Returning to defining the relationship between the Game and Platform models, you'll also need to reciprocate the relationship within the Game model, albeit with somewhat different syntax because this time we're referring to the parent Platform model:

In this snippet we're identifying the foreign keys found in the Game model's associated schema, identifying both the column storing the foreign key (platform_id), and the model that foreign key represents (Platform). Of course, it's entirely likely for a model to store multiple foreign keys. For instance, a model named Account might refer to three other models (State, Country, and Platform, the latter of which is used to identify the account owner's preferred platform):

protected $_referenceMap = array (

),

'Country' => array (

'refTableClass' => 'Application_Model_DbTable_Platform'

)

);

With the models' relationship configured, quite a few new possibilities suddenly become available. For instance, you can retrieve a game's platform name using this simple call:

$game->findParentRow('Application_Model_DbTable_Platform')->name;

Likewise, you can retrieve dependent rows using the findDependentRowset() method. For instance, the following snippet will retrieve the count of games associated with the Xbox 360 platform (identified by a primary key of 1):

you decide to quit supporting this platform and delete it from the platforms table. See the Zend Framework documentation for more information about this feature.

Sorting a Dependent Rowset

When retrieving a a dependent result set (such as games associated with a particular platform), you'll often want to sort these results according to some criteria. To do so, you'll need to pass a query object into the findDependentRowset() method as demonstrated here:

$platformTable = new Application_Model_DbTable_Platform(); $gameTable = new Application_Model_DbTable_Game();

$games = $platformTable->findDependentRowset( 'Application_Model_DbTable_Game', null, $gameTable->select()->order('name')

);

JOINing Your Data

ORM solutions because they effectively abstract much of the gory SQL syntax that I've grown to despise over the years. But being able to avoid the syntax doesn't mean you should be altogether ignorant of it. In fact, ultimately you're going to need to understand some of SQL's finer points in order to maximize its capabilities. This is no more evident than when you need to retrieve related data residing within multiple tables, a technique known as joining tables.

Join Scenarios

If you're not familiar with the concept of a join, this section will serve to acquaint you with the topic by working through several common scenarios which appear within any data-driven website of moderate complexity.

Finding a User's Friends

The typical social networking website offers a means for examining a user's list of friends. There are many ways to manage a user's social connections, however one of the easiest involves simply using a table to associate each user's primary key with the friend's primary key. This table might look like this:

CREATE TABLE friends (

id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, account_id INTEGER UNSIGNED NOT NULL,

friend_id INTEGER UNSIGNED NOT NULL, created_on TIMESTAMP NOT NULL

);

Let's begin by examining the most basic type of join, known as the inner join. An inner join will return the desired rows whenever there is at least one match in both tables, the match being determined by a shared value such as an account's primary key. So for example, you might use a join to retrieve a list of a particular account's friends

mysql>SELECT a.username FROM accounts a

->INNER JOIN friends f ON f.friend_id = a.id WHERE f.account_id = 44;

This join requests the username of each account owner found in the friends table who is mapped to a friend of the account owner identified by 44.

Determine the Number of Copies of a Game Found in Your Network

Suppose you would like to borrow a particular game, but know your friend John had already loaned his copy to Carli. Chances are however somebody else in your network owns the game, but how can you know? Using a simple join, it's possible to determine the number of copies owned by friends, a feature integrated into GameNomad and shown in Figure 6.2.

Figure 6.2. Determining whether an account's friend owns a game

You might notice in Figure 6.2 this feature is actually used twice; once to determine the number of copies found in your network, and a second time to determine the number of copies found in your network which are identified as being available to borrow. To perform the former task, use this SQL join:

mysql>SELECT COUNT(gu.id) FROM games_to_accounts gu ->INNER JOIN friends f ON f.friend_id = gu.account_id ->WHERE f.account_id = 1 AND gu.game_id = 3;

As an exercise, try modifying this query to determine how many copies are available to borrow.

Determining Which Games Have Not Been Assigned a Platform

In an effort to increase the size of your site's gaming catalog, you've acquired another website which was dedicated to video game reviews. While the integration of this catalog has significantly bolstered the size of your database, the previous owner's lackadaisical data management practices left much to be desired, resulting in both incorrect and even missing platform assignments. To review a list

As even these relatively simple examples indicate, join syntax can be pretty confusing. The best advice I can give you is to spend an afternoon leisurely experimenting with the data, creating and executing joins which allow you to view the data in new and interesting ways.

Creating and Executing Zend_Db Joins

Now that you have a better understanding of how joins work, let's move on to how the Zend_Db makes it possible to integrate joins into your website. To demonstrate this feature, consider the following join query, which retrieves a list of a particular account's (identified by the primary key 3) friends:

mysql>SELECT a.id, a.username FROM accounts a ->JOIN friends ON friends.friend_id = a.id ->WHERE friends.account_id = 3;

Using Zend_Db's join syntax, you might rewrite this join and place it within a method named getFriends() found in the Application_Model_DbTable_AccountRow model:

01 function getFriends()

02 {

03 $accountTable = new Application_Model_DbTable_Account(); 04 $query = $accountTable->select()->setIntegrityCheck(false);

05 $query->from(array('a' => 'accounts'), array('a.id', 'a.username')); 06 $query->join(array('f' => 'friends'), 'f.friend_id = a.id', array()); 07 $query->where('f.account_id = ?', $this->id);

08

09$results = $accountTable->fetchAll($query);

10return $results;

11}

Let's break this down:

•The setIntegrityCheck() method used in Line 04 defines the result set as read only, meaning any attempts to modify or delete the result set will cause an exception to be thrown. Although

mysql>SELECT MAX(ranks.id) AS id, games.name AS name, games.asin AS asin, ->games.platform_id AS platform_id,

->ranks.rank AS rank ->FROM games

->JOIN ranks

->ON games.id = ranks.game_id ->GROUP BY ranks.game_id

->ORDER BY ranks.rank LIMIT 100;

Although by no means the most complex of queries, it's nonetheless a mouthful. Wouldn't it be much more straightforward if we can simply call this query using the following alias:

mysql>SELECT view_latest_sales_ranks;

Using MySQL's view feature, you can do exactly this! To create the view, login to MySQL using the mysql client or phpMyAdmin and execute the following command:

mysql>CREATE VIEW view_latest_sales_ranks AS

->SELECT MAX(ranks.id) AS id, games.name AS name, games.asin AS asin, ->games.platform_id AS platform_id,

->ranks.rank AS rank ->FROM games JOIN ranks

->ON games.id = ranks.game_id ->GROUP BY ranks.game_id

->ORDER BY ranks.rank LIMIT 100;

Tip

View creation statements are not automatically updated to reflect any structural or naming changes you make to the view's underlying tables and columns. Therefore if you make any changes to the tables or columns used by the view which reflect the view's SQL syntax, you'll need to modify the view accordingly. Modifying a view is demonstrated in the section "Reviewing View Creation Syntax".

Adding the View to the Zend Framework

The Zend Framework recognizes views as it would any other database table, meaning you can build a model around it!

<?php

class Application_Model_DbTable_ViewLatestSalesRanks extends Zend_Db_Table_Abstract

{