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
.
Calling
setIntegrityCheck(false)
is the proper way to do a join; if you are usingZend_Db_Table
andZend_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 callsave()
ordelete()
on.Here is a quote from the reference guide on Zend_Db_Table - Advanced Usage (skip to example 27.
See also: One-to-Many Joins with Zend_Db_Table_Select
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: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:
This new query only returns the fields from
tableB
, but you can addwhere
conditions on any of the tables, as you would normally do with SQL, with no problem.