How to write sql query in YII2?

Question

Yii

Hello. Already there are not enough nerves, so I ask you to help me compose a sql-query for yii2. The examples from the documentation do not work for me, or rather, I can’t adjust them to my needs. To the point: there are two tables T1(id, name, created_at) and T2(id, user_id, t1_id). What would the following query look like in yii2:

select *
from T1
left join (
  select t2_id
  from T2
  where user_id = 200
) t2 on
    t2.t1_id = t1.id
where created_at > 0

Tried something like this looking at the documentation:

class T1 extends ActiveRecord
{
    public function getT2()
    {
        return $this->hasOne(T2::className(), ['id' => 't1_id']);
    }
}

class T2 extends ActiveRecord
{
    public function getT1()
    {
        return $this->hasOne(T1::className(), ['t1_id' => 'id']);
    }
}

$T2 = T2::find()->where(['=', 'user_id', 200]);
$T1 = $T2->getT1()->where(['>', 'created_at', 0])->orderBy('id desc')->all();

Но получаю ошибку "Calling unknown method: yii\db\ActiveQuery::getT1()"

I would be grateful both for ready-made solutions and for any kind of recommendations.

Answer 1

1) Specifically for your request, you can use code like this:

T1::find()->andWhere(['id in ( select t2_id
  from T2
  where user_id = :user_id) ',['user_id'=>200]])->andWhere(['>', 'created_at', 0])->all()

In this case, you have a subquery saved, and it makes sense to use it only if you know in advance that there are few records there, and you need to pull them out that way.
2) You can rewrite your request as:

select t1.*,t2.t2_id
from T1
left join t2   on
    t2.t1_id = t1.id
where created_at > 0 and t2.user_id=200

This option is more correct, since it uses a full-fledged join.
And then you have two sub-options -
a)
b)
Register connections, you seem to be correct, and then -
In this case, eager loading occurs, and in the model you will be able to get $t1->t2->$param.