Zend Framework Relational Database

Question

I'm trying to figure out how to program referencial mapping with Zend Framework 1.11.x. I have 4 tables: person, cities, states, and countries. Each person has a city_id, state_id, and country_id associated with each row.

I can currently display all the data using fetchAll, but City, State, and Country all show up at numbers rather than their corresponding names. I've tried searching for tutorials, google, etc, but I just can't find a good example of what I want to do.

Here is the code in my person controller:

<?php

class Application_Model_DbTable_Person extends Zend_Db_Table_Abstract
{

protected $_name = 'person';
protected $_primary = 'person_id';

//get individual rows of people
public function getPerson($id)
{
    $id = (int)$id;
    $row = $this->fetchRow('person_id = ' . $id);
    if(!$row) {
        throw new Exception("Could not find row $id");
    }
    return $row->toArray();
}

//adding new persons
public function addPerson($firstName, $lastName, $cityId, $stateId, $countryId, $dob, $zip)
{
    $data = array(
        'first_name' => $firstName,
        'last_name' => $lastName,
        'city_id'   => $cityId,
        'state_id'  => $stateId,
        'country_id'    =>  $countryId,
        'dob'   =>   $dob,
        'zip'   =>  $zip,
    );
    $this->insert($data);
}

//updating an existing person
public function updatePerson($id, $firstName, $lastName, $cityId, $stateId, $countryId, $dob, $zip)
{
    $data = array(
        'first_name' => $firstName,
        'last_name' => $lastName,
        'city_id'   => $cityId,
        'state_id'  => $stateId,
        'country_id'    =>  $countryId,
        'dob'   =>   $dob,
        'zip'   =>  $zip,
    );
    $this->update($data, 'person_id = '. (int)$id);
}

//deleting a person
public function deletePerson($id)
{
    $this->delete('person_id = ' . (int)$id);
}
}

I've also attached a picture of what I am looking at from a user interface perspective. If I can figure this out, this concept will help me get a lot further with my development.

I want to avoid using the select(); and focus on Zend's code, I know how to do this with basic SQL, but I'm trying to learn the whole MVC framework.

http://imageshack.us/photo/my-images/29/screenshot20110705at711.png/

Also, here is my controller code:

<?php

class PersonController extends Zend_Controller_Action
{

public function init()
{
    /* Initialize action controller here */
}

public function indexAction()
{
    $person = new Application_Model_DbTable_Person();
    $this->view->person = $person->fetchAll();
}

public function addAction()
{
    // action body
}

public function editAction()
{
    // action body
}

public function deleteAction()
{
    // action body
}
}

There isnt too much point to learn Zend_Db. Zend_Db isnt worth the overhead. Doctrine on the other hand is, as is Propel. Youd be better off learning one of those and how to integrate with Zend. Otherwise just use PDO. I have yet to see a set of requirements where Zend_Db was the right choice. — prodigitalson
The relational structure seems odd to me. A city is contained in a state and a state is contained in a country. Once we know a person's city, then his state and country follow from that; no need to specify city and state independently in the person record, which opens the possibility of inconsistency. I'd make person point to city, city point to state, and state point to country. Then @Marcin's join approach still works great, just need to modify the join conditions. — David Weinraub

Marcin Marcin · Accepted Answer · 2011-07-06T05:05:18

Just quickly what I have observed.

From the code that you provided, I assume that you have defined dependent tables for City, State, and Country, i.e. they should indicate Person model as dependent.

One way to solve your problem would be to define custom Row class for a person, e.g.

class My_Model_Table_Row_Person extends Zend_Db_Table_Row_Abstract {    

    /**
     * Get City Row for the current person row.
     *
     * @return Zend_Db_Table_Row
     */
    public function getCity() {
        return $this->findParentRow('Application_Model_DbTable_City');
    }

    // similar for State and Country
}

And in your Person model:

class Application_Model_DbTable_Person extends Zend_Db_Table_Abstract {
   // need to add this
   protected $_rowClass = 'My_Model_Table_Row_Person';
}

With this, the fethAll method in your action would return rowsets of My_Model_Table_Row_Person instances. Then for each row you could easly get parent tabtles of a person as follows:

$personRowset = $person->fetchAll();
foreach($personRowset as $person) {
   $cityName = $person->getCity()->name;    
}

Second way would be to define a VIEW in your database, that has the columns you want and you would define a ZF db model for the view. ZF does not care if it works with actual tables or views, as long as it knows primary id (in a model for the view, you would need to define what is your primary key, i.e. protected $_primary = array('id');).

Third way would be to perform JOIN sql query in your Person model for example:

  class Application_Model_DbTable_Person extends Zend_Db_Table_Abstract {

     /**
     * Get all persons and their data 
     * 
     * @return Zend_Db_Table_Rowset
     */
    public function fetchAllPersons() {

        $select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
                ->setIntegrityCheck(false);

        $select->joinInner('CITY', 'PERSON.city_id = CITY.id', 'CITY.name as cityName')
                 ->joinInner('SATE', 'PERSON.state_id = STATE.id', 'STATE.name as stateName');
                 ->joinInner('COUNTRY', 'PERSON.country_id = COUNTRY.id', 'COUNTRY.name as countryName');


        return $this->fetchAll($select);    
    }

Hope this helps.

Zend Framework Relational Database

1 Answers