Complex JOIN clause not supported

[brenard]

Hello,

I need to make a complex query like this one :

SELECT
    c.id_contract,
    COALESCE(credit.total_credit, 0) AS total_credit,
    COALESCE(debit.total_debit, 0) AS total_debit,
    COALESCE(credit.total_credit, 0) + COALESCE(debit.total_debit, 0) AS current_balance
FROM
    contracts c
LEFT JOIN (
    SELECT id_contract, SUM(amount) AS total_credit
    FROM credit_operations
    GROUP BY id_contract
) credit ON c.id_contract = credit.id_contract
LEFT JOIN (
    SELECT id_contract, SUM(amount) AS total_debit
    FROM debit_operations
    GROUP BY id_contract
) debit ON c.id_contract = debit.id_contract;

With FluentPDO, it's may be done with the following code :

$query = $fpdo -> from('contracts')
    -> select("COALESCE(credit.total_credit, 0) AS total_credit")
    -> select("COALESCE(debit.total_debit, 0) AS total_debit")
    -> select("COALESCE(credit.total_credit, 0) + COALESCE(debit.total_debit, 0) AS current_balance")
    -> disableSmartJoin()
    -> leftJoin(
        "(
            SELECT id_contract, SUM(value) AS total_credit
            FROM credit_operations
            GROUP BY id_contract
        ) credit ON admin_contract.id_contract = credit.id_contract")
    -> leftJoin(
        "(
            SELECT id_contract, SUM(cost) AS total_debit
            FROM debit_operations
            GROUP BY id_contract
        ) debit ON admin_contract.id_contract = debit.id_contract")

To day, it's not working, because the second JOIN clause is ignored :

echo($query->getQuery());
SELECT contracts.*, COALESCE(credit.total_credit, 0) AS total_credit, COALESCE(debit.total_debit, 0) AS total_debit, COALESCE(credit.total_credit, 0) + COALESCE(debit.total_debit, 0) AS solde
FROM contracts
    LEFT JOIN (
                    SELECT id_contract, SUM(value) AS total_credit
FROM credit_operations
WHERE valid = TRUE
GROUP BY id_contract
                ) credit ON contracts.id_contract = credit.id_contract

It's due to the method Envms\FluentPDO\Regex::tableAlias() that does not handle this case.

A quick fix could be :

public function tableAlias(string $subject, &$matches = null)
{
    if (
        preg_match(
            '/^\s*(\(.*\))\s+(?:AS\s+)?([' . self::SQLCHARS . ']+)\s+ON\s/uis',
            $subject,
            $matches
        )   
    )   
        return 1;
    return preg_match(
        '/`?([' . self::SQLCHARS . ']+[.:]?[' . self::SQLCHARS . '*]*)`?(\s+AS)?(\s+`?([' . self::SQLCHARS . ']*)`?)?/ui',
        $subject,
        $matches
    );  
}

What do you think about this fix ?

Note: The tableAlias() method seem currently only used by Envms\FluentPDO\Queries\Common::setJoinNameAlias().