Zend 2 join with select

Question

I have to make a join with a select in Zend 2,is there a way to do it?I saw that join expect first paremetr to be a string( name of the table you join). I manage to did a select with another select using something like this

$this->select(function (Select $select) use ($params)

But again join(function (Select $select) ) doesn't work.

Maybe this will help -> http://stackoverflow.com/questions/13626527/executing-multiple-join-with-expressions-on-zend-framework-2 — Crisp, Jan 16 '15 at 17:07
The following example will guide you: https://gist.github.com/ralphschindler/3949548 or do not use the zend way and just create your query string and the execute. Comment if you would like an example of regular query and no the zend way — dixromos98, Jan 19 '15 at 10:45
I know how to create query string and execute,this is what I use now,but I want to change and use objects.I would like an example of a join with select in the zend way if you know how to do it. — WebDeveloper, Jan 22 '15 at 12:50

score 0 · Answer 1 · answered Jan 15 '15 at 16:21

0

A join has two required parameters, the name of the table and the condition to join on. You can also optionally specify columns to return and the join type, which defaults to an inner join. Here is a quick example to fetch the author for a post.

// First you need to create the select object
$select = $sql->select();
$select->from('posts');

// then join to the people table
$select->join('people', 'posts.author_id = people.id', array('first_name','last_name'));

answered Jan 15 '15 at 16:21

ForrestLyman

1,544
2
17
24

I know all that,I've made simple joins ,my problem is how to make a join with a select.Something like this: select * from YTable y inner join (select min(price),id from Xtable group by id) x on y.id = x.id – WebDeveloper Jan 15 '15 at 16:32
I think the only way to do this would be to create your own select object and refactor the join method. – ForrestLyman Jan 15 '15 at 16:48

score 0 · Answer 2 · answered Feb 23 '18 at 06:32

    Please add below code on top of model class
    ------------------------------------------
    use Zend\Db\Sql\Expression;
    use Zend\Db\Sql\Predicate;
    use Zend\Db\Sql\Sql;

    Then You can use this code
    --------------------------
    $sql = new Sql($this->adapter);
    $select = $sql->select();
    $select->from(array('nxyr' => 'node-x-y-relation'));
    $join = new Expression("ni.node_id = nxyr.node_x_id and ni.node_type_id IN (" . $nodeTypeStr . ")");
    $join2 = new Expression("np.node_id = nxyr.node_x_id and np.node_type_id = 2");
    $join3 = new Expression("nc.node_id = nxyr.node_x_id and nc.node_type_id = 2");
    $select->join(array('nc' => 'node-class'), $join3, array('node_type_id'), 'Left');
    $select->join(array('ni' => 'node-instance'), $join, array('node_type_id'), 'Left');
    $select->join(array('np' => 'node-instance-property'), $join2, array('node_type_id'), 'Left');

    $select->where->equalTo('nxyr.node_y_id', $node_id);
    $statement = $sql->prepareStatementForSqlObject($select);

    $result = $statement->execute();
    $resultObj = new ResultSet();
    $nodeXYArr = $resultObj->initialize($result)->toArray();

Zend 2 join with select

2 Answers2