Zend SQL 联合查询的问题

Question

我在使用 Zend Framework 进行联合查询时遇到问题。

查询如下：

$localizedEvents = $db->select()
                                        ->from(array("cont" => "content"))
                                        ->where("cont.contentType = ?", 'event')
                                        ->where('cont.realm = ?', 'live')
                                        ->join(array('contCat' => 'content_categories'), 'cont.id = contCat.id_content', array())
                                        ->join(array('cats' => 'categories'), 'contCat.id_category = cats.id')
                                        ->where('cats.title like ?', "%$this->keyword%")
                                        ->distinct();
                                        
            $eventsQuery = $db->select()->from(array("cont" => "content"))
                                        ->where("cont.contentType = ?", 'event')
                                        ->where('cont.content LIKE ? ', "%$termEncoded%")
                                        ->where('cont.realm = ?', 'live');
            
            $finalQuery = $db->select()->union(array($localizedEvents, $eventsQuery))->order('cont.publishDate DESC');

生成的查询如下：

SELECT  `cont`. * ,  `cats`. * 
FROM  `content` AS  `cont` 
INNER JOIN  `content_categories` AS  `contCat` ON cont.id = contCat.id_content
INNER JOIN  `categories` AS  `cats` ON contCat.id_category = cats.id
WHERE (

cont.contentType =  'event'
)
AND (
cont.realm =  'live'
)
AND (
cats.title LIKE  '%conferência%'
)
UNION SELECT  `cont`. * 
FROM  `content` AS  `cont` 
WHERE (
cont.contentType =  'event'
)
AND (
cont.content LIKE  '%confer\\\\u00eancia%'
)
AND (
cont.realm =  'live'
)
ORDER BY  `cont`.`publishDate` DESC 
LIMIT 0 , 30

这将返回此错误：

1222 - 使用的 SELECT 语句具有不同数量的列

我不知道我做错了什么。有人可以帮我吗？

所需的 SQL 查询应该是：

SELECT  `cont`. * 
FROM  `content` AS  `cont` 
INNER JOIN  `content_categories` AS  `contCat` ON cont.id = contCat.id_content
INNER JOIN  `categories` AS  `cats` ON contCat.id_category = cats.id
WHERE (
cont.contentType =  'event'
)
AND (
cont.realm =  'live'
)
AND (
cats.title LIKE  '%conferência%'
)
UNION SELECT  `cont`. * 
FROM  `content` AS  `cont` 
WHERE (
cont.contentType =  'event'
)
AND (
cont.content LIKE  '%confer\\\\u00eancia%'
)
AND (
cont.realm =  'live'
)
LIMIT 0 , 30

有人可以帮助我如何将此查询转换为 Zend 吗？

Answer 1

您正在合并两个 SELECT 查询，但这两个查询应该具有相同的列数。在第一个查询中，您选择以下字段：

`cont`. * ,  `cats`. *

在第二个查询中，您选择以下字段：

`cont`. *

Answer 2

在

->join(array('contCat' => 'content_categories'), 'cont.id = contCat.id_content', array())
->join(array('cats' => 'categories'), 'contCat.id_category = cats.id')

You use an empty array for contCat and none for cats 时，请尝试向 cats 添加一个空数组，因为据我所知，否则它会选择 * all 。

无论如何，看看查询看起来如何。