Yii2 alias to parent model table when using relation via and comparison operators

Question

I have Yii2 relations

Model Variants (simplified)

Variants hasOne Reference Sequence

public function getReferenceSequence()
{
    return $this->hasOne(ReferenceSequences::className(), ['id' => 'reference_sequence_id'])->alias('referenceSequence');
}

Variants hasMany Annotations via ReferenceSequence

I want to join Annotations based on the model property 'start' and 'end'(integer values) using comparison operators.

THIS DOESN'T WORK

public function getAnnotations()
    {
        return $this->hasMany(Annotations::className(), ['chrom' => 'name'])
            ->via('referenceSequence')
            ->andOnCondition(['AND',['>=','tx_start',$this->start],['<=','tx_start',$this->end]])
            ->alias('annotations');
    }

I'm looking for the Annotations that have the same chromosome, but also fall within the start/end range for the Variants model.

Instead of trying to inject the model properties $this->start and $this->end which for some reason only results in one pair of values being used, rather than each search result model joining on the properties of the $this object, I want to use an alias to the parent table.

The 'on' condition should be something like:

->andOnCondition(['AND',['>=','tx_start','VARIANTS_MODEL_ALIAS.start],['<=','tx_start',VARIANTS_MODEL_ALIAS.end]])

In Yii1 there was the magic "t" alias but this doesn't work any more.

rob006 rob006 · Accepted Answer · 2019-06-02T09:30:16

Conditions like ->andOnCondition(['AND',['>=','tx_start',$this->start],['<=','tx_start',$this->end]]) will work only with lazy loading, since $this will refer to current model which run this query. If you do Variants::find() query is run from empty model, so start and end will be always empty (or have default value).

If you want to make this relation working you may try something like this:

public static function find() {
    return parent::find()->alias('variants');
}

public function getAnnotations() {
    return $this->hasMany(Annotations::className(), ['chrom' => 'name'])
        ->via('referenceSequence')
        ->andOnCondition([
            'AND',
            ['>=', 'tx_start', new Expression('variants.start')],
            ['<=', 'tx_start', new Expression('variants.end')],
        ])
        ->alias('annotations');
}

public function getReferenceSequence() {
    return $this->hasOne(ReferenceSequences::className(), ['id' => 'reference_sequence_id'])
        ->alias('referenceSequence');
}

This may work for joins (like Variant::find()->joinWith('annotations')->all(), but you will not be able to use this relation for lazy loading, and most likely you will not be able even to fetch annotations using this relation. This case is tricky, you may need to use trick from this answer or change your DB schema to simplify the whole problem.

Yii2 alias to parent model table when using relation via and comparison operators

1 Answers