Thursday, December 10, 2009

Using table relationships


In this example, we implement relationships between tables.

Components used in this example
Implementation of the queries

class MyRelationships
{
Storing the database connection

    public function __construct($db)
    {
        
$this->_db $db;
    }
Processing the query
  • We get the object identifier and the max number of rows to display, from the GET request.
  • We define the default adapter.
  • We get the bug details, the bug status and who reported the bug.
  • Or we get the status and the bugs with that status.
  • Or we get who reported the bug and the bugs reported by that person.
  • Or we get all bugs, all statuses, and all people who report the bugs.
  • If we catch an exception, we return the error message.

    public function process()
    {
        
// We get the object identifier and the max number of rows to display,
        // from the GET request.
        
list($bugId$statusId$reportedById$limit) = $this->_getParameters();

        try {
            
// We define the default adapter.
            
Zend_Db_Table_Abstract::setDefaultAdapter($this->_db);

            if (
$bugId) {
                
// We get the bug details, the bug status and who reported the bug.
                
$result $this->_getBugParents($bugId$limit) ;
            } else if (
$statusId) {
                
// Or we get the status and the bugs with that status.
                
$result $this->_getBugs('MyStatus'$statusId$limit);
            } else if (
$reportedById) {
                
// Or we get who reported the bug and the bugs reported by that person.
                
$result $this->_getBugs('MyReportedBy'$reportedById$limit);
            } else {
                
// Or we get all bugs, all statuses, and all people who report the bugs.
                
$result = array(
                    
'BUG LIST' => $this->_getTable('MyBug'$limit),
                    
'STATUS LIST' => $this->_getTable('MyStatus'$limit),
                    
'REPORTED-BY LIST' => $this->_getTable('MyReportedBy'$limit),
                );
            }

        } catch (
Exception $e) {
            
// If we catch an exception, we return the error message.
            
$result $e->getMessage();
        }

        return array(
$bugId$statusId$reportedById$limit$result);
    }
Extraction of the parameters from the GET request

    private function _getParameters()
    {
        
$bugId = isset($_GET['bug'])? $_GET['bug'] : null;
        
$statusId = isset($_GET['status'])? $_GET['status'] : null;
        
$reportedById = isset($_GET['reported_by'])? $_GET['reported_by'] : null;
        
$limit = isset($_GET['limit'])? $_GET['limit'] : null;

        return array(
$bugId$statusId$reportedById$limit);
    }
Getting a bug with its status and who reported the bug
  • We find the bug.
  • We find the status.
  • We find who reported the bug.

    private function _getBugParents($bugId$limit)
    {
        
// We find the bug.
        
$bugTable = new MyBug();
        
$bug $bugTable
            
->find($bugId)
            ->
current();

        if (
$bug) {
            
// We find the status.
            
$status $bug->findParentRow('MyStatus') and
            
$status $status->toArray();

            
// We find who reported the bug.
            
$reportedBy $bug->findParentRow('MyReportedBy') and
            
$reportedBy $reportedBy->toArray();

            
$result = array(
                
'BUG' => $bug->toArray(),
                
'STATUS' => $status,
                
'REPORTED BY' => $reportedBy,
            );
        } else {
            
$result = array();
        }

        return 
$result;
    }
Getting the list of bugs
  • We find the parent
  • We find the bugs of that parent.

    private function _getBugs($class$id$limit)
    {
        
// We find the parent
        
$table = new $class;
        
$parent $table
            
->find($id)
            ->
current();

        if (
$parent) {
            
$select $table->select()
                ->
order('name ASC')
                ->
limit($limit);

            
// We find the bugs of that parent.
            
$rows $parent
                
->findDependentRowset('MyBug'$class$select)
                ->
toArray();
        } else {
            
$rows = array();
        }

        return 
$rows;
    }
Getting the rows of a table

    private function _getTable($class$limit)
    {
        
$table = new $class;

        
$select $table->select()
            ->
order('name ASC')
            ->
limit($limit);

        return 
$table->fetchAll($select)->toArray();
    }

}

No comments:

Post a Comment