0

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?

Community
  • 1
  • 1
nunohora
  • 5
  • 3

2 Answers2

2

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`. *
Martijn
  • 5,491
  • 4
  • 33
  • 41
  • Well, if I add to the query: [$eventsQuery = $db->select()->from(array("cont" => "content", "cats" => "categories")) it stays the same. What's your advice? – nunohora Mar 07 '11 at 17:11
0

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.

Wouter van Tilburg
  • 232
  • 1
  • 8