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.

Mooney

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.

Mooney

Yes that works, but as you pointed out you can't get any of the data from the joined tables in your result set. Since the data still IS from the primary table, then you haven't violated the integrity of the result, "all of your data belong to primary table". It is useful though if you want to only get rows that have the foreign key from table A. Thanks for pointing that out. – Ptyalism

with integrity check disabled I could retrieve from both primary and joined tables. Thanks for the info @Mooney – Shriner

Answer 2

Calling setIntegrityCheck(false) is the proper way to do a join; if you are using Zend_Db_Table and Zend_Db_Table_Select, you can't join unless you disable the integrity check.

The integrity check is simply in place to make sure the query DOES NOT use multiple tables, and when in place, ensures that the Zend_Db_Table_Row objects can be deleted or modified and then saved because the row data is exclusive to a single table, and is not a mix of data from different tables.

To indicate that you WANT to use multiple tables, then specify setIntegrityCheck(false) to let Zend Framework know that it is intentional. The result is that you get a locked row which cannot call save() or delete() on.

Here is a quote from the reference guide on Zend_Db_Table - Advanced Usage (skip to example 27.

The Zend_Db_Table_Select is primarily used to constrain and validate so that it may enforce the criteria for a legal SELECT query. However there may be certain cases where you require the flexibility of the Zend_Db_Table_Row component and do not require a writable or deletable row. for this specific user case, it is possible to retrieve a row or rowset by passing a FALSE value to setIntegrityCheck(). The resulting row or rowset will be returned as a 'locked' row (meaning the save(), delete() and any field-setting methods will throw an exception).

See also: One-to-Many Joins with Zend_Db_Table_Select

Ptyalism