zend framework 2 join subselect using oracle adapter

Question

I've searched throughout the internet and mostly answers apply to ZF1.
I'd like to join a subselect query into another select using select objects. It's crucial for me to get whole Select object at the end of multiple such joins.

Code looks like this:

$subSelect = new Select();
$subSelect->from(array('TABLE' => 'SOME_TABLE'));
$subSelect->columns(array('ID'));
$select = new Select();
$select->from(array('SEC_TABLE' => 'SOME_ANOTHER_TABLE'));
$select->join(
    array('SUB' => $subSelect),
    'SUB.ID = SEC_TABLE.ID',
    array(),
    $select::JOIN_LEFT
);

It was possible in ZF1 using class Zend_Db_Expr, is there any nice way to this in ZF2?

Community · Accepted Answer · 2017-05-23 12:10:05Z

0

This is answered here

In ZF2 you can combine two Select object instance using its Select::combine() method.

$select->combine($subSelect);

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered May 9, 2017 at 11:31

unclexo

3,9812 gold badges22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arkadiusz Galler Over a year ago

I'm not sure if it's gonna work as expected, because combine makes UNION and I need JOIN with parameters. I've been trying different approaches for hours now, I will try this one too.

unclexo Over a year ago

Well, to be honest, I did not use Oracle before. BTW, did you try it as $select->join($subSelect, ... ). As it can take the Select instance too as the first argument.

Arkadiusz Galler Over a year ago

It can in standard Adapter, Oracle Adapter overwrites it, so it can't take Select as argument.

Arkadiusz Galler · Accepted Answer · 2017-05-09 12:08:39Z

I tried to use Select::combine() but it is functionality used for things such as UNION or INTERSECT or MINUS - keywords placed between select statements. I need pure JOIN and the problem is that Oracle Adapter in ZF2 overwrites standard approach to processJoins method. Here is the code from Zend/Db/Sql/Select

protected function processJoins(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
    {
        if (!$this->joins) {
            return null;
        }

        // process joins
        $joinSpecArgArray = array();
        foreach ($this->joins as $j => $join) {
            $joinSpecArgArray[$j] = array();
            $joinName = null;
            $joinAs = null;

            // type
            $joinSpecArgArray[$j][] = strtoupper($join['type']);

            // table name
            if (is_array($join['name'])) {
                $joinName = current($join['name']);
                $joinAs = $platform->quoteIdentifier(key($join['name']));
            } else {
                $joinName = $join['name'];
            }
            if ($joinName instanceof ExpressionInterface) {
                $joinName = $joinName->getExpression();
            } elseif ($joinName instanceof TableIdentifier) {
                $joinName = $joinName->getTableAndSchema();
                $joinName = ($joinName[1] ? $platform->quoteIdentifier($joinName[1]) . $platform->getIdentifierSeparator() : '') . $platform->quoteIdentifier($joinName[0]);
            } else {
                if ($joinName instanceof Select) {
                    $joinName = '(' . $this->processSubSelect($joinName, $platform, $driver, $parameterContainer) . ')';
                } else {
                    $joinName = $platform->quoteIdentifier($joinName);
                }
            }
            $joinSpecArgArray[$j][] = (isset($joinAs)) ? $joinName . ' AS ' . $joinAs : $joinName;

            // on expression
            // note: for Expression objects, pass them to processExpression with a prefix specific to each join (used for named parameters)
            $joinSpecArgArray[$j][] = ($join['on'] instanceof ExpressionInterface)
                ? $this->processExpression($join['on'], $platform, $driver, $this->processInfo['paramPrefix'] . 'join' . ($j+1) . 'part')
                : $platform->quoteIdentifierInFragment($join['on'], array('=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>')); // on
            if ($joinSpecArgArray[$j][2] instanceof StatementContainerInterface) {
                if ($parameterContainer) {
                    $parameterContainer->merge($joinSpecArgArray[$j][2]->getParameterContainer());
                }
                $joinSpecArgArray[$j][2] = $joinSpecArgArray[$j][2]->getSql();
            }
        }

        return array($joinSpecArgArray);
    }

and here from Zend/Db/Platform/Oracle/Select/SelectDecorator which extends standard Select class and is used for Oracle database:

protected function processJoins(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if (!$this->joins) {
    return null;
}

// process joins
$joinSpecArgArray = array();
foreach ($this->joins as $j => $join) {
    $joinSpecArgArray[$j] = array();
    // type
    $joinSpecArgArray[$j][] = strtoupper($join['type']);
    // table name
    $joinSpecArgArray[$j][] = (is_array($join['name']))
        ? $platform->quoteIdentifier(current($join['name'])) . ' ' . $platform->quoteIdentifier(key($join['name']))
        : $platform->quoteIdentifier($join['name']);
    // on expression
    $joinSpecArgArray[$j][] = ($join['on'] instanceof ExpressionInterface)
        ? $this->processExpression($join['on'], $platform, $driver, $this->processInfo['paramPrefix'] . 'join')
        : $platform->quoteIdentifierInFragment($join['on'], array('=', 'AND', 'OR', '(', ')', 'BETWEEN')); // on
    if ($joinSpecArgArray[$j][2] instanceof StatementContainerInterface) {
        if ($parameterContainer) {
            $parameterContainer->merge($joinSpecArgArray[$j][2]->getParameterContainer());
        }
        $joinSpecArgArray[$j][2] = $joinSpecArgArray[$j][2]->getSql();
    }
}

return array($joinSpecArgArray);
}

As standard Select class allows you to join another Select ( line : if ($joinName instanceof Select) {), Oracle extended version doesn't.
Is it just a bug in Zend?
Is there any way around it?
It's not in any way connected to Oracle specification, why is it so then?

Collectives™ on Stack Overflow

zend framework 2 join subselect using oracle adapter

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related