Possible issue with Join

[tyrsson]

Package Version

2.2x.x

Php Version

8.1

Database Engine

None

Basic Information

Have not had time to research this but found it while reviewing questions on StackOverflow. We need to verify if this is still an issue or not or if its even valid.

https://stackoverflow.com/questions/77314273/is-there-a-method-for-incorporating-a-conditional-clause-within-a-laminas-db-sel

Steps to Reproduce

Expected Behavior

No response

Actual behavior?

Not confirmed

Additional Info

No response

[simon-mundy]

It's not an issue per-se - the Db Driver Platform is responsible for quoting the ON condition, and it expects identifiers as default.

However, common-sense dictates that Db columns will never be numeric, so it's also not correct to assume that an integer should be auto-quoted as if it were an identifier.

So, flagging this change could be added to Laminas\Db\Adapter\Platform\AbstractPlatform

foreach ($parts as $part) {
    $identifier .= isset($safeWordsInt[strtolower($part)])
        ? $part
        : $this->quoteIdentifier[0]
        . str_replace($this->quoteIdentifier[0], $this->quoteIdentifierTo, $part)
        . $this->quoteIdentifier[1];
}

can be changed to

foreach ($parts as $part) {
    $identifier .= isset($safeWordsInt[strtolower($part)]) || is_numeric($part)
        ? $part
        : $this->quoteIdentifier[0]
        . str_replace($this->quoteIdentifier[0], $this->quoteIdentifierTo, $part)
        . $this->quoteIdentifier[1];
}

[simon-mundy]

Can confirm that the new refactor-sql-abstraction resolves this very elegantly.

$select->join(array('b' => 'custom_table1'), "a.id = b.column1_id AND b.column2_id = 1", array('b' => 'custom_column_name'), Select::JOIN_LEFT);

now becomes

$condition = new Expression("a.id = b.column1_id AND b.column2_id = ?", [1]) // Arguments default to ArgumentType of Value
$condition = new Expression("a.id = b.column1_id AND b.column2_id = ?", new Argument(1)) // or
$condition = new Expression("a.id = b.column1_id AND b.column2_id = ?", new Argument(1, ArgumentType::Value))

$select->join(
    ['b' => 'custom_table1'], 
    $condition,
    ['b' => 'custom_column_name'],
    Select::JOIN_LEFT
);