Zend Framework Table Relationships - select from multiple tables within app

Question

I hope I'm asking this question in an understandable way. I've been working on an app that has been dealing with 1 table ( jobschedule ). So, I have models/Jobschedule.php, models/JobscheduleMapper.php, controllers/JobscheduleController.php, view/scripts/jobschedule/*.phtml files

So in my controller I'll do something like this:

    $jobnumber = $jobschedule->getJobnum();
    $jobtype = $jobschedule->getJobtype();

    $table = $this->getDbTable();

public function listAction()
{
    $this->_helper->layout->disableLayout();
    $this->view->jobnum = $this->getRequest()->getParam( 'jobnum', false );
    $this->view->items = array();       

    $jobschedule = new Application_Model_Jobschedule();
    $jobschedule->setJobnum( $this->view->jobnum );

    $mapper = new Application_Model_JobscheduleMapper();
    $this->view->entries = $mapper->fetchAll ( $jobschedule );
}

and then in my mapper I I do something like:

        $resultSet = $table->fetchAll($table->select()->where('jobnum = ?', $jobnumber)->where('jobtype = ?', $jobtype) );
        $entries   = array();
        foreach ($resultSet as $row) {
            $entry = new Application_Model_Jobschedule();
            $entry->setJobnum($row->jobnum)
                  ->setJobtype($row->jobtype)
                  ->setJobdesc($row->jobdesc)
                  ->setJobstart($row->jobstart)
                  ->setJobend($row->jobend)
                  ->setJobfinished($row->jobfinished)
                  ->setJobnotes($row->jobnotes)
                  ->setJobid($row->jobid);
            $entries[] = $entry;
        }
        return $entries;
    }

Then in my view I can manipulate $entries. Well, the problem I'm coming across now is that there is also another table called 'jobindex' that has a column in it called 'jobno'. That 'jobno' column holds the same record as the 'jobnum' column in the 'jobschedule' table. I need to find the value of the 'store_type' column in the 'jobindex' table where jobindex.jobno = joschedule.jobnum ( where 1234 is the jobno/jobnum for example ). Can someone please help me here? Do I need to create a jobindex mapper and controller? If so, that's done ... I just don't know how to manipulate both tables at once and get the record I need. And where to put that code...in my controller?

score 0 · Answer 1 · 2012-04-11T22:02:41.220

0

If I'm understanding you correctly, you'll want to join the 'jobindex' table to the 'jobschedule' table.

...
$resultSet = $table->fetchAll(
    $table->select()->setIntegrityCheck(false)
        ->from($table, array('*'))
        ->joinLeft(
            'jobindex',
            'jobindex.jobno = jobschedule.jobnumber',
            array('store_type'))
        ->where('jobnum = ?', $jobnumber)
        ->where('jobtype = ?', $jobtype)
        ->where('jobindex.store_type = ?', $_POST['store_num'])
);
....

Depending on how 'jobschedule' is related to 'jobindex', you may want an inner join (joinInner()) instead.

The setIntegrityCheck(false) disables referential integrity between the tables, which is only important if you are writing to them. For queries like this one, you can just disable it and move on (else it will throw an exception).

edited Apr 11 '12 at 22:02

answered Apr 11 '12 at 01:41

I'm going to try it soon. Thanks very much for your help, please stay tuned. – Tommy Ash Apr 11 '12 at 01:45
I think I have really confused myself. I'm going to try an explain again and see if you come up with something different. jobschedule has a column in it called jobnum, and jobindex table has a column called jobno. Each of these hold the same value. The jobindex table also has a row called store_type. I want to find all records where jobno and jobnum match AND store_type == some param I pass in my POST. So, if ?store_type=WM, I want to display on the page all records from the jobschedule table where store_type from the jobindex table equals WM AND that record's jobno = jobnum from jobschedule – Tommy Ash Apr 11 '12 at 02:59
I'm trying to be as specific as possible and make sense. If you guys need me to try better to explain I will...this has been driving me nuts for a couple of days. – Tommy Ash Apr 11 '12 at 03:00
Ok, I think you would just need to add another where() to filter on store_type. I'll edit my answer above. – Apr 11 '12 at 22:01

score 0 · Answer 2 · answered Apr 11 '12 at 08:06

If I understand you correctly this is the SQL query you need to extract the data from database:

SELECT `jobschedule`.* FROM `jobschedule` INNER JOIN `jobindex` ON jobindex.jobno = jobschedule.jobnum WHERE (jobindex.jobtype = 'WM')

Assembling this SQL query in Zend would look something like this:

    $select->from('jobschedule', array('*'))
    ->joinInner(
        'jobindex',
        'jobindex.jobno = jobschedule.jobnum',
        array())
    ->where('jobindex.jobtype = ?', $jobtype);

Let us know if that's what you are looking for.

Zend Framework Table Relationships - select from multiple tables within app

2 Answers2