How to use union in zend db

Question

In sql i am using union i don't know how to write it in zend db.

select m.*, 0 as is_shared from test m where user_id = $userId 
union
select m.*,1 as is_shared from test m 
join test_shares ms 
where m.test_id = ms.test_id 
and ms.email_address = $email 
and m.url is not null;

Please help me out....

I tried like this but no use

$cols1 = array('test.*,0 as is_shared');
$select1 = $db->select ()
    ->from ( 'test', $cols1 )
    ->where ( 'user_id = ?', $userId);

$cols2 = array('test_shares.*', '1 as is_shared');
$select2 = $db->select ()
    ->from ( 'test', $cols2 )
    ->join ( 'test_shares', array () )
    ->where ( 'test.test_id = test_shares.test_id')
    ->where ( 'test_shares.email_address = ?',  $email)
    ->where ( 'test.url is NOT NULL');

$select = $db->select()
             ->union(array($select1, $select2))
             ->order('title');

It is taking 'test'.'0' AS 'is_shared' like this but i need like this 0 as is_shared. It is taking 'test'.'1' AS 'is_shared' like this but i need like this 1 as is_shared.

Solution 1

I don't know our sql is work. But it can make following code.

$userId = 10;
$email = '[email protected]';

$select1 = $db->select()
                ->from(array('m' => 'test'), array('*', '0 AS is_shared'))
                ->where('user_id =?', $userId);

$select2 = $db->select()
                ->from(array('m' => 'test'), array('*', '1 AS is_shared'))
                ->join(array('ms' => 'test_shares'), 'm.test_id = ms.test_id', '')
                ->where('ms.email_address =?', $email)
                ->where('m.url IS NULL');                  

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

echo $select; die;

        /*SELECT `m`.*, `m`.`0` AS `is_shared` 
         * FROM `test` AS `m` 
         * WHERE (user_id =10) 
         * UNION 
         * SELECT `m`.*, `m`.`1` AS `is_shared` 
         * FROM `test` AS `m` 
         * INNER JOIN `test_shares` AS `ms` 
         * ON m.test_id = ms.test_id 
         * WHERE (ms.email_address ='[email protected]') AND (m.url IS NULL) 
         * ORDER BY `title` ASC*/

Solution 2

For future reference, in Zend Framework 2.3 this is done with combine.

For example:

    use \Zend\Db\Sql\Select;
    use \Zend\Db\Sql\Sql;

    $sql = new Sql(/* ADAPTER HERE */);

    $tag1 = new Select( ['a' => 'articles'] );
    $tag1->columns( [ 'tag' => 'first_tag'] );
    $tag1->where->in('a.id', $articleIds);

    $tag2 = new Select( ['a' => 'articles'] );
    $tag2->columns( [ 'tag' => 'second_tag'] );
    $tag2->where->in('a.id', $articleIds);
    $tag2->combine($tag1);

    $tag3 = new Select( ['a' => 'articles'] );
    $tag3->columns( [ 'tag' => 'third_tag'] );
    $tag3->where->in('a.id', $articleIds);
    $tag3->combine($tag2);

    $statement = $sql->prepareStatementForSqlObject($tag3);

Solution 3

According to the Zend_Db_Select documentation, you can create one query for each member of the union (they can be strings or Zend_Db_Select objects themselves), and then call the union() method of Zend_Db_Select.

Something like:

$sql1 = FIRSTPARTOFTHEQUERY;
$sql2 = SECONDPARTOFTHEQUERY;
$select = $db->select();
$select->union(array($sql1, $sql2));

Hope that helps,