setIntegrityCheck in Zend Selects with joins

Question

I was looking at some questions that ask how to do joins in Zend Framework queries, but the answer is always something like "just do setIntegrityCheck(FALSE)".

My question is: why do I need to do this?

It seems to me disabling "integrity checks" is not the proper way of making this work. In my particular case, I'm using a MySQL database with some InnoDB tables with foreign keys, so for example:

CREATE TABLE IF NOT EXISTS `tableA`
(
`id` CHAR(6),
`name` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tableB`
(
`tableA_id` CHAR(6),
`somefield` VARCHAR(255),
PRIMARY KEY (`tableA_id`)
) ENGINE=InnoDB;

ALTER TABLE `tableB` ADD FOREIGN KEY fk1 (`tableA_id`) REFERENCES `tableA` (`id`);

(this is a very simplified version of my DB)

And, my query code looks like this:

$table = new Zend_Db_Table('tableB');
$select = $table->select(TRUE)
  ->join(array('a' => 'tableA'), 'tableB.tableA_id = a.id');
$result = $table->fetchAll($select);

This is giving me the "Select query cannot join with another table" exception unless I add the setIntegrity(FALSE) to my $select.

Answer 1

Ok, I did some research, and it isn't quite true that you have to call setIntegrityCheck(FALSE) in order to do joins.

The relevant code in the Zend_Db_Select class (i.e. the only place to find the very last word to this argument), contains this code:

if ($this->_integrityCheck !== false) {
    foreach ($fields as $columnEntry) {
        list($table, $column) = $columnEntry;

        // Check each column to ensure it only references the primary table
        if ($column) {
            if (!isset($from[$table]) || $from[$table]['tableName'] != $primary) {
                require_once 'Zend/Db/Table/Select/Exception.php';
                throw new Zend_Db_Table_Select_Exception('Select query cannot join with another table');
            }
        }
    }
}

So, actually, it checks to see if all the selected fields in the query belong to the "primary table". A query does not necessarily have to return all the fields in the involved tables.

Coming back to the example in my question, it turns out this does work:

$table = new Zend_Db_Table('tableB');
$select = $table->select(TRUE)
  ->join(array('a' => 'tableA'), 'tableB.tableA_id = a.id', NULL); // <-- notice the third parameter here
$result = $table->fetchAll($select);

This new query only returns the fields from tableB, but you can add where conditions on any of the tables, as you would normally do with SQL, with no problem.