Problem with Zend SQL Union Query

Question

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?

Martijn · Accepted Answer · 2011-03-07 17:07:04Z

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`. *

answered Mar 7, 2011 at 17:07

Martijn

5,6314 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nunohora Over a year ago

Well, if I add to the query: [$eventsQuery = $db->select()->from(array("cont" => "content", "cats" => "categories")) it stays the same. What's your advice?

Wouter van Tilburg · Accepted Answer · 2016-11-16 09:18:35Z

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.

edited Nov 16, 2016 at 9:18

answered Mar 7, 2011 at 18:13

Wouter van Tilburg

2321 silver badge8 bronze badges

Collectives™ on Stack Overflow

Problem with Zend SQL Union Query

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related