ActiveQuery joinWith eager loading compound relation error

[joe-meyer]

I've found that when trying to do a joinWith on a hasMany relation that tries to join on more than 1 column, I receive malformed SQL which results in a failure. Sample code of what producdes the error can be seen in the findPartCommentsSummary function below

class CdDet extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function getDb()
    {
        return Yii::$app->mfg;
    }

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'cd_det';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['oid_cd_det'], 'number'],
            [['cd_cmmt'], 'string', 'max' => 1370],
            [['cd_domain'], 'string', 'max' => 8],
            [['cd_lang', 'cd_type'], 'string', 'max' => 30],
            [['cd_ref', 'cd_user1', 'cd_user2', 'cd__qadc01'], 'string', 'max' => 80],
            [['cd_seq'], 'string', 'max' => 4]
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'cd_cmmt' => Yii::t('app', 'Comment Data'),
            'cd_domain' => Yii::t('app', 'Domain'),
            'cd_lang' => Yii::t('app', 'Language'),
            'cd_ref' => Yii::t('app', 'Master Reference'),
            'cd_seq' => Yii::t('app', 'Page'),
            'cd_type' => Yii::t('app', 'Type'),
            'cd_user1' => Yii::t('app', 'Ufld1'),
            'cd_user2' => Yii::t('app', 'Ufld2'),
            'cd__qadc01' => Yii::t('app', 'Cd  Qadc01'),
            'oid_cd_det' => Yii::t('app', 'Oid Cd Det'),
        ];
    }

    public function getParts()
    {
        return $this->hasMany(PtMstr::className(), ['pt_domain' => 'cd_domain', 'pt_part' => 'cd_ref']);
    }

    public static function findPartCommentsSummary($domain, $items = array()){
        return CdDet::find()
            ->select('pt_domain, pt_part, pt_desc1, pt_desc2, cd_lang, cd_type, cd_domain, cd_ref, cd_seq')
            ->from('{{cd_det}}')
            ->innerJoinWith('parts')
            ->one();
    }
}

Call Stack:

2015-01-07 17:11:04 [::1][-][-][error][yii\db\Exception] exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 0 [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about ", "pt_part") IN ((?, ?))" (10713) (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)' in C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php:214
Stack trace:
#0 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php(214): PDO->prepare('SELECT * FROM p...')
#1 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php(815): yii\db\Command->prepare(true)
#2 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php(350): yii\db\Command->queryInternal('fetchAll', NULL)
#3 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Query.php(206): yii\db\Command->queryAll()
#4 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(130): yii\db\Query->all(NULL)
#5 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveRelationTrait.php(237): yii\db\ActiveQuery->all()
#6 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQueryTrait.php(168): yii\db\ActiveQuery->populateRelation('parts', Array)
#7 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(223): yii\db\ActiveQuery->findWith(Array, Array)
#8 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(287): yii\db\ActiveQuery->populate(Array)
#9 C:\Users\jmeyer\IdeaProjects\DataPoints\models\CdDet.php(89): yii\db\ActiveQuery->one()
#10 C:\Users\jmeyer\IdeaProjects\DataPoints\controllers\SiteController.php(50): app\models\CdDet::findPartCommentsSummary('100', Array)
#11 [internal function]: app\controllers\SiteController->actionIndex()
#12 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array)
#13 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Controller.php(151): yii\base\InlineAction->runWithParams(Array)
#14 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('', Array)
#15 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\web\Application.php(83): yii\base\Module->runAction('', Array)
#16 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
#17 C:\Users\jmeyer\IdeaProjects\DataPoints\web\index-test.php(17): yii\base\Application->run()
#18 {main}

Next exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 0 [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about ", "pt_part") IN ((?, ?))" (10713) (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)
Failed to prepare SQL: SELECT * FROM pt_mstr WHERE ("pt_domain", "pt_part") IN ((:qp0, :qp1))' in C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php:219
Stack trace:
#0 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php(815): yii\db\Command->prepare(true)
#1 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Command.php(350): yii\db\Command->queryInternal('fetchAll', NULL)
#2 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\Query.php(206): yii\db\Command->queryAll()
#3 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(130): yii\db\Query->all(NULL)
#4 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveRelationTrait.php(237): yii\db\ActiveQuery->all()
#5 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQueryTrait.php(168): yii\db\ActiveQuery->populateRelation('parts', Array)
#6 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(223): yii\db\ActiveQuery->findWith(Array, Array)
#7 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\db\ActiveQuery.php(287): yii\db\ActiveQuery->populate(Array)
#8 C:\Users\jmeyer\IdeaProjects\DataPoints\models\CdDet.php(89): yii\db\ActiveQuery->one()
#9 C:\Users\jmeyer\IdeaProjects\DataPoints\controllers\SiteController.php(50): app\models\CdDet::findPartCommentsSummary('100', Array)
#10 [internal function]: app\controllers\SiteController->actionIndex()
#11 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array)
#12 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Controller.php(151): yii\base\InlineAction->runWithParams(Array)
#13 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('', Array)
#14 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\web\Application.php(83): yii\base\Module->runAction('', Array)
#15 C:\Users\jmeyer\IdeaProjects\DataPoints\vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
#16 C:\Users\jmeyer\IdeaProjects\DataPoints\web\index-test.php(17): yii\base\Application->run()
#17 {main}
Additional Information:
Array
(
    [0] => HY000
    [1] => 0
    [2] => [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about ", "pt_part") IN ((?, ?))" (10713) (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)
    [3] => 
)

[joe-meyer]

For what it's worth this seems to be an issue with the buildCompositeInCondition function in teh QueryBuilder. Swapping it out with this seems to work (although might be able to be prettied up a bit):

    protected function buildCompositeInCondition($operator, $columns, $values, &$params)
    {
        $vss = [];
        foreach ($values as $value) {
            $vs = [];
            foreach ($columns as $column) {
                if (isset($value[$column])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value[$column];
                    $vs[] = $phName;
                } else {
                    $vs[] = 'NULL';
                }
            }
            $vss[] = $vs;
        }
        foreach ($columns as $i => $column) {
            if (strpos($column, '(') === false) {
                $columns[$i] = $this->db->quoteColumnName($column);
            }
        }
        $condition = '';
        foreach ($vss AS $vs) {
            $condition .= '(';
            for($i = 0; $i < count($vs); $i++) {
                $condition .= '(' . $columns[$i] . ' ' . $operator . ' (' . $vs[$i] . ')) AND ';
            };
            $condition = substr($condition, 0, -4);
            $condition .= ') OR ';
        }
        $condition = substr($condition, 0, -3);

        return $condition;
    }

[nineinchnick]

As a reference, here's PostgreSQL docs about row constructors.

It is not supported in SQLite and MySQL afaik.

I will provide a PR with a unit test with @EC-Joe's change.

[joe-meyer]

@nineinchnick is this still something you're planning on looking at? If not is this something that could be put on a road map somewhere? If more information needs to be provided please let me know so that I can help the issue move forward. Thanks

[nineinchnick]

Yeah, sorry for the delay, I'll have some free time today to pick it up.

[nineinchnick]

Updated the PR.

[joe-meyer]

Great work. Thanks @nineinchnick!