Querying Two or More Tables using JOIN

Demonstrating the benefits of using the Zend_Db_Select statement requires you to expand on simply retrieving a specific artist. You now want to retrieve all the fans for a specific artist. In the application, users have lists of artists they listen to; if users contain an artist, they are considered to be a fan of that artist.

Constructing the POSQL statement requires two tables: artists and accounts_artists. The new POSQL statement is the following:

SELECT "a"."id" AS "artist id", "a"."artist_name" AS "name", "a"."genre", "aa"."account_id" AS "user_id", "aa"."created_date" AS "date_became_fan" FROM "artists" AS "a" INNER JOIN "accounts_artists" AS "aa" ON aa.artist_id = a.id

The Zend_Db_Select object contains six types of JOIN statements that can be executed successfully if the RDBMS supports it. Zend_Db_Select supports inner joins, left joins, right joins, full joins, natural joins, and cross joins. The statement uses the INNER JOIN SQL call, so you use the join() method. A full list of available join() methods can be seen in Table 5-5.

Table 5-5. Join Methods

Method Description Parameters join() OR joinInner() INNER JOIN SQL call join(table name, join condition, columns to retrieve)

Example:

join(array("tableAlias" => "tableName"), "table1.id = table2.id", array("column_alias" => "column));

(Continued)

joinLeft()

LEFT JOIN SQL call

joinLeft(table name, join condition, columns to retrieve) Example:

join(array("tableAlias" => "tableName"), "tablel.id = table2.id", array("column_alias" => "column));

joinRight()

RIGHT JOIN SQL call

joinRight(table name, join condition, columns to retrieve) Example:

joinRight(array("tableAlias" => "tableName"), "tablel.id = table2.id", array("column_alias" => "column));

joinFull()

FULL JOIN SQL call

joinFull(table name, join condition, columns to retrieve) Example:

joinFull(array("tableAlias" => "tableName"), "tablel.id = table2.id", array("column_alias" => "column));

joinCross()

CROSS JOIN SQL call

joinCross(table name, columns to retrieve) Example:

joinCross(array("tableAlias" => "tableName"), array("column_alias" => "column));

joinNatural()

NATURAL JOIN SQL call

joinNatural(table name, columns to retrieve) Example:

joinNatural(array("tableAlias" => "tableName"), array("column_alias" => "column));

Taking the inner join used in the preceding statement, use the join() method call:

• The first parameter accepts a mixed type. It can be a key-value array where the key is the alias you want to use for the table, and the value is the name of the table you want to use.

• The second parameter is the condition on which you want to join the tables.

• The third parameter contains the columns you want to fetch from the table. This parameter also accepts a key-value array where the key is the alias to the column and the value is the column name.

Let's now implement a join() method and transform the query to an object-oriented statement. Open the ArtistController.php file and create a new action: testoofansAction.

The new action created in the ArtistController.php file is shown in Listing 5-32 and uses much of the code created in the previous examples. Again, you create a $columns variable that contains the column you want to retrieve as well as specify the $tableInfo variable that contains the alias and the table you want to fetch data from.

Listing 5-32. testoofansAction using join()

public function testoofansAction(){

//Create DB object require_once "Db/Db_Db.php"; $db = Db_Db::conn();

//Create the statement

//SELECT "a"."id" AS "artist id", "a"."artist_name" AS "name", //"a"."genre",aa"."account_id" AS "user_id", //"aa"."created_date" AS "date_became_fan" //FROM "artists" AS "a"

//INNER JOIN "accounts_artists" AS "aa" ON aa.artist_id = a.id $select = new Zend_Db_Select($db);

//Determine which columns to retrieve. //Determine which table to retrieve data from. $columns = array("artist id" => 'a.id', "name" => 'a.artist_name', "genre" => 'a.genre');

$statement = $select->from($tableInfo, $columns) ->join(array("aa" => 'accounts_artists'), 'aa.artist_id = a.id', array("user_id" => 'aa.account_id', "date_became_fan" => 'aa.created_date'));

$results = $db->query($statement); $rows = $results->fetchAll();

//Compare Statement echo $statement->_toString();

//Supress the View

$this->_helper->viewRenderer->setNoRender();

After the columns and the table are set, you can use the join() method. You supply it with an array containing the alias you want to give the accounts_artists table: aa. You also supply the join condition, which specifies the column that will associate the records to each other in the artists and accounts_artists tables. In this case, you are using the artists.id and the accounts_artists.artist_id columns. Finally, you supply the third parameter with an array containing the columns and its aliases you want to use.

Load the URL http://localhost/artist/testoofans to see the resulting POSQL that is created.

Was this article helpful?

0 0

Post a comment