nunohora nunohora - 16 days ago 8
MySQL Question

Problem with Zend SQL Union Query

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?

Answer

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.

Comments