Zend Framework Select Objects And UNION()
Solution 1
Zend_Db_Select has a union method so I'd have thought it is possible, if you can build your query using a select object. I haven't used Zend_Db_Select (or the table subclass) with union but I'd imagine you can do something like
$select = $this->select()
->where('blah')
->union($sql);
Solution 2
Here's what I've done to make a union:
$select = $this->select();
//common select from both sides of the union goes here
$select1 = clone($select);
//select1 specifics here
$select2 = clone($select);
//select 2 specifics here
$db = $this->getAdapter();
$pageselect = $db->select()->union(array("($select1)", "($select2)"));
Remember Db_Select
's __toString
will print out the SQL generated by that select, to help you debug.
Solution 3
a complete example:
public function getReservationById($id)
{
if(!$id) return null;
$sql = $this->table->select();
$sql->union(array(
$this->table->select()->where('id=?', $id),
$this->tableFinished->select()->where('id=?', $id),
$this->tableCanceled->select()->where('id=?', $id),
$this->tableTrashed->select()->where('id=?', $id)
));
echo $sql->__toString();
}
and the generated query:
SELECT reservations
.* FROM reservations
WHERE (id='5658') UNION SELECT res_finished
.* FROM res_finished
WHERE (id='5658') UNION SELECT res_cancel
.* FROM res_cancel
WHERE (id='5658') UNION SELECT res_trash
.* FROM res_trash
WHERE (id='5658')
Solution 4
This practical example shows a function that returns a rowset of either latest or if a available favourite blog entries of a specific year (artwork blog):
public function fetchBestOf($year)
{
$selectLatest = $this->select()->where('isHidden = 0')
->where('YEAR(dateCreated) = ' . $year)
->where('isHighlight = 0');
$selectHighlights = $this->select()->where('isHidden = 0')
->where('YEAR(dateCreated) = ' . $year)
->where('isHighlight = 1');
$selectUnion = $this->select()->union(array($selectLatest, $selectHighlights), Zend_Db_Select::SQL_UNION_ALL)
->order('isHighlight DESC')
->order('dateCreated DESC')
->order('workID DESC')
->limit('5');
$rowset = $this->fetchAll($selectUnion);
return $rowset;
}
Solution 5
The best way Zend suggest is like follows....
$sql = $this->_db->select()
->union(array($select1, $select2,$select3))
->order('by_someorder');
echo $sql->__toString();
$stmt = $db->query($sql);
$result = $stmt->fetchAll();
echo will show the query
Here $select1, $select2, $select3 can be different select queries with same number of columns...
Richard Knop
I'm a software engineer mostly working on backend from 2011. I have used various languages but has been mostly been writing Go code since 2014. In addition, I have been involved in lot of infra work and have experience with various public cloud platforms, Kubernetes, Terraform etc. For databases I have used lot of Postgres and MySQL but also Redis and other key value or document databases. Check some of my open source projects: https://github.com/RichardKnop/machinery https://github.com/RichardKnop/go-oauth2-server https://github.com/RichardKnop
Updated on June 04, 2022Comments
-
Richard Knop almost 2 years
I'm pretty sure this is not possible in Zend Framework (I have searched the Web, the documentation and issue tracker) but I just want to make sure so I'm asking here.
$select = $this->select(); $select->union($select1, $select2);
That doesn't work of course. To explain what I need. I need to use UNION() to merge 2 tables in a SELECT query, I know I could just do:
$select = "$select1 UNION $select2";
The problem is that would return a string and I need to get a select object so I can use it with Zend_Paginator.
I have already solved the issue by modifying my database architecture but I'm just curious if there is some workaround for this.