CODEWITHSUNDEEP
mysqlzend-frameworkzend-framework2zend-dbzend-framework3
Lavish Tyagi
Lavish Tyagi

Reputation: 233

Union of two tables in Zend 2

I want to union two tables with where clause in zf2:-

table1 app_followers
table2 app_users
where condition could be anything
and order by updated_date.

Please let me know the query for zend 2.

Thanks..

Upvotes: 3

Views: 458

Answers (2)

Serhii Popov
Serhii Popov

Reputation: 3804

I wanted to do a similar task and spent a lot of time while to figure out how to do that in the right way.

The idea with Laminas\Db\Sql\Combine is really well but you cannot apply the ordering to this object and as the result, it's useless in this case.

Finally, I ended up with the next code:

$skill = $sql->select('skill');
$language = $sql->select('language');
$location = $sql->select('location');
$occupation = $sql->select('occupation');

$skill->combine($language);
$language->combine($location);
$location->combine($occupation);

$combined = (new Laminas\Db\Sql\Select())
    ->from(['sub' => $skill])
    ->order(['updated_date ASC']);

However, it's a bit messy with parentheses. If it's a matter for you, please check this comment on Github, but on MySQL id doesn't matter, not sure about other databases.

Upvotes: 1

Mohd Belal
Mohd Belal

Reputation: 1189

Using UNION is ZF2:

Using ZF2 dedicated class Combine Zend\Db\Sql\Combine

    new Combine(
     [
      $select1,
      $select2,
      $select3,
       ...
     ]
    )

A detailed example which uses combine is as follows:

$select1 = $sql->select('java');
$select2 = $sql->select('dotnet');
$select1->combine($select2);

$select3 = $sql->select('android');

$selectall3 = $sql->select();
$selectall3->from(array('sel1and2' => $select1));
$selectall3->combine($select3);

$select4 = $sql->select('network');

$selectall4 = $sql->select();
$selectall4->from(array('sel1and2and3' => $selectall3));
$selectall4->combine($select4);

$select5 = $sql->select('dmining');

$selectall5 = $sql->select();
$selectall5->from(array('sel1and2and3and4' => $selectall4));
$selectall5->combine($select5);

which is equivalent to the normal SQL query for UNION:

SELECT * FROM java 
UNION SELECT * from dotnet 
UNION SELECT * from android 
UNION SELECT * from network;
UNION SELECT * from dmining;

I hope it helps.

Upvotes: 4

Related Questions