ZF2 and nested sets - how to do cross join using Select object?

Question

To fetch a tree in nested sets we have to do a cartesian product (every with every).

select * from tree as t1, tree as t2

or

select * from tree as t1 cross join tree as t2

It was easy with zf1, but in zf2 there is no cross join when using \Zend\Db\Sql\Select object?

Is there any way to achieve it using Select()? Or plain sql query is the only way?

Googling failed me unfortunately, so thanks in advance for any suggestions.

Answer 1

It is possible to pass type of join as last argument

class Select extends AbstractSql implements SqlInterface, PreparableSqlInterface
{
    const JOIN_INNER = 'inner';
    const JOIN_OUTER = 'outer';
    const JOIN_LEFT = 'left';
    const JOIN_RIGHT = 'right';
    const SQL_STAR = '*';
    const ORDER_ASCENDING = 'ASC';
    const ORDER_DESENDING = 'DESC';

    public $where; // @param Where $where

    public function __construct($table = null);
    public function from($table);
    public function columns(array $columns, $prefixColumnsWithTable = true);
    public function join($name, $on, $columns = self::SQL_STAR, $type = self::JOIN_INNER);
    public function where($predicate, $combination = Predicate\PredicateSet::OP_AND);
    public function group($group);
    public function having($predicate, $combination = Predicate\PredicateSet::OP_AND);
    public function order($order);
    public function limit($limit);
    public function offset($offset);
}

So, zf1 method joinCross(table, [columns]) equivalents to zf2 join($name, $on, $columns, 'cross')

Answer 2

A CROSS JOIN operation can be replaced with an (INNER) JOIN where the join clause always evaluates to true (1=1)

select * from tree as t1 inner join tree as t2 on 1=1

Zf2 example:

$select = new Sql\Select;

$select ->from(array('node' => 'nested_category'))
        ->columns(array('node_name' => new Sql\Expression('CONCAT(REPEAT(" ", COUNT(parent.name)-1), node.name)')))
        ->join(array('parent' => 'nested_category'), new Sql\Expression('1=1'), array('id'))
        ->where('node.lft BETWEEN parent.lft AND parent.rgt')
        ->group('node.name')
        ->order('node.lft');

$resultSet = $this->selectWith($select);