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

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