Zend Selects 中的 setIntegrityCheck 与连接 [英] setIntegrityCheck in Zend Selects with joins

问题描述

我在看一些 问题 询问如何在 Zend Framework 查询中进行连接，但答案总是类似于只需执行 setIntegrityCheck(FALSE)".

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)".

我的问题是:为什么我需要这样做?

在我看来，禁用完整性检查"并不是进行这项工作的正确方法.在我的特殊情况下，我使用 MySQL 数据库和一些带有外键的 InnoDB 表，例如:

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);

这给了我 Select query cannot join with another table" 异常，除非我将 setIntegrity(FALSE) 添加到我的 $select>.

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

推荐答案

好的，我做了一些研究，你必须调用 setIntegrityCheck(FALSE) 这不是完全正确 以进行连接.

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

Zend_Db_Select 类中的相关代码(即唯一可以找到该参数的最后一个词的地方)，包含以下代码:

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);

这个新查询只返回 tableB 中的字段，但您可以在任何表上添加 where 条件，就像您通常使用 SQL 所做的那样，没有问题.

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.

这篇关于Zend Selects 中的 setIntegrityCheck 与连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！