setIntegrityCheck in Zend Selects with joins
zend-db-select, zend-framework
Solution
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.
Problem
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`.