Zend Framework Select Objects And UNION()

Question

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.

Justin · Accepted Answer · 2009-08-24 14:39:43Z

13

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.

answered Aug 24, 2009 at 14:39

Justin

5,0691 gold badge23 silver badges22 bronze badges

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

Comments

David Snabel-Caunt · Accepted Answer · 2009-08-23 21:34:37Z

7

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

answered Aug 23, 2009 at 21:34

David Snabel-Caunt

58.4k13 gold badges118 silver badges132 bronze badges

Comments

Luiggi ZAMOL · Accepted Answer · 2010-10-29 11:38:58Z

4

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

answered Oct 29, 2010 at 11:38

Luiggi ZAMOL

793 bronze badges

Comments

markus · Accepted Answer · 2011-11-21 13:54:55Z

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;
}

besin · Accepted Answer · 2012-05-22 13:34:26Z

2

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

answered May 22, 2012 at 13:34

besin

1064 bronze badges

Comments

karthikr · Accepted Answer · 2012-10-11 02:45:28Z

1

This is how it works for me:

$select1 = $this->select();               
$select2 = $this->select();

After getting the necessary data in both queries the UNION syntax goes like this:

 $select = $this->select()->union(array('('.$select1.')', '('.$select2.')'));

edited Oct 11, 2012 at 2:45

karthikr

100k26 gold badges208 silver badges191 bronze badges

answered Jul 20, 2012 at 13:01

Petar Djordjevic

991 silver badge4 bronze badges

Collectives™ on Stack Overflow

Zend Framework Select Objects And UNION()

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related