I'm having trouble making a Union query using the Zend Framework.

The queries are these:

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

the generated query is as follows:

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

This returns me this error:

1222 - The used SELECT statements have a different number of columns

I have no idea what I'm doing wrong. Can somebody help me please?

The desired SQL Query should be:

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

Can somebody help me how to turn this query into Zend?

You're UNIONing two SELECT queries, but both those queries are supposed to have the same column count. In the first query, you select the following fields:

`cont`. * ,  `cats`. *

In the second query you select these fields:

`cont`. *

On

->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, try adding an empty array to cats as well, because as far as i know it will select * all otherwise.

Anyways see how the query looks then.