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.

Answer 1

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

Answer 2

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