It is impossible to create relation with "not equal" condition. OnCondition not supports conditions for columns

[Insolita]

trafficstars

Required query

SELECT "documents".*,"dup".*
FROM "documents"
    LEFT JOIN "documents" "dup"
        ON ("documents"."filehash" = "dup"."filehash"
                AND "documents"."company_id" = "dup"."company_id"
                AND "documents"."id" != "dup"."id"
            )

Try to define a relation in model Document

   public function getDuplicates()
    {
        return $this->hasMany(Document::class, ['filehash' => 'filehash', 'company_id' => 'company_id'])
               ->alias('dup')
            ->onCondition(new Expression('"dup"."id" != "documents"."id"'));

    }

Query

$query = new ActiveQuery(Document::class, $db);
$query->alias('documents')->joinWith('duplicates')->all();

Fails with error

SQLSTATE[42P01]: Undefined table: 7 ERROR: invalid reference to FROM-clause entry for table "documents"
LINE 1: ..._id") IN (($1, $2), ($3, $4))) AND ("dup"."id" != "documents...
^
HINT: Perhaps you meant to reference the table alias "dup".
The SQL being executed was: SELECT * FROM "documents" "dup" WHERE (("filehash", "company_id") IN (('e53d24bd42e888cd7d7824132f1fd5a0c9ed9fe2', 5), ('e53d24bd42e888cd7d7824132f1fd5a0c9ed9fe2', 5))) AND ("dup"."id" != "documents"."id")

But command SQL is OK :

$query = new ActiveQuery(Document::class, $db);
$query->alias('documents')->joinWith('duplicates')->createCommand()->getRawSql();

return SELECT "documents".* FROM "documents" "documents" LEFT JOIN "documents" "dup" ON ("documents"."filehash" = "dup"."filehash" AND "documents"."company_id" = "dup"."company_id") AND ("dup"."id" != "documents"."id")

The problem comes from ActiveQuery populate

https://github.com/yiisoft/db/blob/master/src/Query/Query.php#L203

https://github.com/yiisoft/active-record/blob/master/src/ActiveQuery.php#L250

https://github.com/yiisoft/active-record/blob/02b5ea1f7363d2b8a6908818f6be26cd41cdacee/src/ActiveQueryTrait.php#L161

This method replaces values from relation "link" to real values, but it doesn't touch "onCondition" expression, https://github.com/yiisoft/active-record/blob/02b5ea1f7363d2b8a6908818f6be26cd41cdacee/src/ActiveRelationTrait.php#L548

This method executes wrong query https://github.com/yiisoft/active-record/blob/02b5ea1f7363d2b8a6908818f6be26cd41cdacee/src/ActiveRelationTrait.php#L304

[darkdef]

If we fix you problem - in populate be cyclical relation For example, for link relation duplicates need be create sql with left join documents, and result query be incorrect (with select fromdocuments & join document)

For your problem - simple add where condition

$query = new ActiveQuery(Document::class, $db);
$query->alias('documents')->joinWith('duplicates')
->andWhere(new Expression('{{dup}}.{{id}} != {{documents}}.{{id}}'))
->all();

And i recommend use for quote {{tableName}}.{{columnName}} - for compatible with other db drivers

[Insolita]

->andWhere(new Expression('{{dup}}.{{id}} != {{documents}}.{{id}}'))

Unfortunately, a result of populated models with this query is not correct and contains duplicated ids in $model->duplicates ; The valid result will be only if add additional grouping clause and aggregate duplicated records, or ids with array_agg/json_agg into an additional property or another kind of hacks So, SQL is ok - the problem in the model population

[Tigrov]

Unfortunately, a result of populated models with this query is not correct and contains duplicated ids in $model->duplicates ; The valid result will be only if add additional grouping clause and aggregate duplicated records, or ids with array_agg/json_agg into an additional property or another kind of hacks So, SQL is ok - the problem in the model population

You can also use indexBy option by unique field or Closure to remove duplicate records.