PHP Zend Framework - How do I use union all?

You need to pass an array of Zend_Db_Select into the union() method. The second parameter to be passed to the union() method to perform the type of UNION. In your case, use Zend_Db_Select::SQL_UNION_ALL as a constant.

For e.g.

$sql1 = $db->select();
$sql2 = "SELECT ...";

$select = $db->select()
    ->union(array($sql1, $sql2), Zend_Db_Select::SQL_UNION_ALL );

Reference: http://framework.zend.com/manual/1.12/en/zend.db.select.html#zend.db.select.building.union

From API Docs:

union( array $select = array, $type = self ) : Zend_Db_Select

Adds a UNION clause to the query.

The first parameter has to be an array of Zend_Db_Select or sql query strings.

$sql1 = $db->select();  
$sql2 = "SELECT ...";  
$select = $db->select()  
             ->union(array($sql1, $sql2))
             ->order("id");

The same example and some additional text can be found in the Example #28 Example of union() method in the ZF Reference guide. Apart from that, you can always use Zend_Db_Expr when you dont need the Query Builder.

$select = union($select1, $select2, self::SQL_UNION_ALL).

Ex:

$select1 = 'select A from table1';
$select2 = 'select A from table2';

$select = $db->select()->union($select1, $select2, Zend_Db_Select::SQL_UNION_ALL);

echo $select;

Result: (SELECT A FROM TABLE1) UNION ALL (SELECT A FROM TABLE2).

here is the running example

in raw Sql

select CONCAT_WS(' ', u.fname, u.lname) as user_name, u.image, c.* from challenge c inner join challenge_user cu on (cu.challenge_id = c.id) inner join users u on (u.id = c.user_id) where c.user_id = '1' group by c.id

union all 
select CONCAT_WS(' ', u.fname, u.lname) as user_name, u.image, c.* from challenge c inner join challenge_user cu on (cu.challenge_id = c.id) inner join users u on (u.id = c.user_id) where concat('%,',c.challenger_user_id,',%') LIKE concat( '%,"1",%' ) group by c.id

in zend

 $sql1 = $this->select()->setIntegrityCheck(FALSE)
      ->from(array('c'=>'challenge'),array('user_name'=>new Zend_Db_Expr("CONCAT_WS(' ',                      u.fname, u.lname)"),'u.image'))
        ->joinInner(array('cu'=>'challenge_user'), 'cu.challenge_id = c.id')
         ->joinInner(array('u'=>'users'),'u.id = c.user_id')
         ->where('c.user_id = ?','1')
          ->group('c.id');
 $sql2 = $this->select()->setIntegrityCheck(FALSE)->from(array('c'=>'challenge'),array('user_name'=>new Zend_Db_Expr("CONCAT_WS(' ', u.fname, u.lname)"),'u.image'))
               ->joinInner(array('cu'=>'challenge_user'), 'cu.challenge_id = c.id')
               ->joinInner(array('u'=>'users'),'u.id = c.user_id')
               ->where(new Zend_Db_Expr("concat('%,',c.challenger_user_id,',%') LIKE concat( '%,"1",%' )"))->group('c.id');

$select = $this->select()->union(array($sql1,$sql2),  Zend_Db_Select::SQL_UNION_ALL);

$data =  $this->fetchAll($select);

Hope it will help some one