php - 如何让 Zend_Db 仅从大型查询中选择一列？

Question

我有一个相当复杂的 SQL 查询，它对两个表执行计数。查询应如下所示:

SELECT 
((SELECT COUNT(DISTINCT(pages.id)) AS `count` 
    FROM `pages` 
    INNER JOIN `pageRegions` ON pageRegions.pageId = pages.id 
    WHERE (MATCH (pages.name, pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE))) + 
(SELECT COUNT(documents.id) AS `count` 
    FROM `documents` 
    INNER JOIN `files` ON files.id = documents.file 
    WHERE (MATCH (documents.name, 
              files.name, 
              files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS count

不幸的是，当我使用 Zend_Db 编写以下代码时，查询包含大量附加列，因此添加两个查询显然会失败:

$total_count_select = $PagesTable->getAdapter()
    ->query('((' . $pages_total_count_select . ') + 
              (' . $legal_resources_total_count_select . ')
             ) AS count');

其中 $pages_total_count_select 和 $legal_resources_total_count_select 都是 Zend_Db_Select 对象。

我尝试使用columns()方法来指定每个选择对象上我想要的列，如下所示:

$legal_resources_total_count_select->columns('COUNT(documents.id) AS count');

但这似乎只是附加到选择查询上，而不是仅返回指定的列。

所以 Zend 生成的查询最终看起来像这样:

SELECT ((SELECT `pages`.*, 
                 ((1.3 * (MATCH(pages.name) AGAINST ('+keyword*' IN BOOLEAN MODE))) + 
                       (0.8 * (MATCH(pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS `score`, 
                     `pages`.`name` AS `page_name`, 
                     `pages`.`id` AS `page_id`, 
                     `pageRegions`.*, 
                     COUNT(DISTINCT(pages.id)) AS `count`
         FROM `pages`
         INNER JOIN `pageRegions` ON pageRegions.pageId = pages.id
         WHERE (MATCH (pages.name, pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE))
         ORDER BY `score` DESC)

            + 

          (SELECT `documents`.*,
                   ((1.3 * (MATCH(documents.title) AGAINST ('+keyword*' IN BOOLEAN MODE))) + 
                       (0.8 * (MATCH(documents.short_description, files.NAME, files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS `score`,
                     `files`.*, 
                    COUNT(documents.id) AS `count`
         FROM `documents`
         INNER JOIN `files` ON files.id = documents.file
         WHERE (MATCH (documents.title, documents.short_description, files.name, files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE))
         ORDER BY `score` DESC)
        ) AS COUNT

如何删除它选择的所有额外列？

Answer 1

您忘记了构建 $pages_total_count_select 和 $legal_resources_total_count_select 的代码部分。

您在构建这些参数的过程中错过了一个参数，即您想要的列数组，如果您不提供它，它将需要表。*。

因此，只需检查创建选择的方式并添加一个带有空数组的参数即可。

更新:所以你这样做:

$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);

之后，用您的 Zend_Db_Table (此处为 $this)重做 ->from() 调用，您可以在此处指定列，因此可以是一个空数组或您想要的唯一数组:

$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->from($this, array(new Zend_Db_Expr('COUNT(documents.id) AS count')));