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

[Insolita]

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

Document::find()->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);
$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/yii2/blob/master/framework/db/Query.php#L251

https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L224

https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQueryTrait.php#L151

This method replaces values from relation "link" to real values, but it doesn't touch "onCondition" expression,

https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveRelationTrait.php#L517

This method executes the wrong query

https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveRelationTrait.php#L270

(Same for yii3 https://github.com/yiisoft/active-record/issues/153)

[ntesic]

@Insolita This could be possible with my changes introduced in #19137 So it would look like this:

class Document extends ActiveRecord
{
    public function getDuplicates()
    {
        return $this->hasMany(Document::class, ['filehash' => 'filehash', 'company_id' => 'company_id']);
    }
}

        $query = New ActiveQuery(Document::class);
        $query->joinWith(['duplicates' => function ($q) use ($query) {
            $q->andOnCondition(['NOT', ['id' => new Expression($query->field('id'))]]);
        }])->all();

And it will generate query:

SELECT `document`.* FROM `document` LEFT JOIN `document` `Document<duplicates>` ON (`document`.`filehash` = `Document<duplicates>`.`filehash` AND `document`.`company_id` = `Document<duplicates>`.`company_id`) AND (NOT (`Document<duplicates>`.`id`=`document`.`id`))

So you don't need to do any alias, even if join with same table