Yii2属性由虚拟属性+属性，Union Sql到Yii2 AR组成

Question

First post, but I've been here for months, never registred (till today). Thank you for all your help.

The problem:

I've got two tables: T1 & T2

T1 is table of devices, T2 is table of documents for devices. Each device has few documents with dates data_plan. When in T2 data_plan is just date, in T1 data_plan (virtual attribute) is calculated by NOW() + interval of co_zostalo. The data_plan as interval of days may be got only when co_data = 1 in T1.
The data_plan from T2 must be the earliest date.

I haven't got any problems with getting value: in T1 model

public function getNextActionDate() {
    $actModel = new Dzialania();
    $data = new \DateTime();
    if ($this->co_data == 1 && $this->co_zostalo != NULL){
        $data->add(new \DateInterval('P'.$this->co_zostalo.'D'));
        return $data->format('Y-m-d');
    } else if ($this->co_data == 0){ //modul czasookresu wylaczony
        $data = $actModel::find()->where(['id_przyrz_d' => $this->id_przyrz, 'wyk' => 'Nie'])->orderBy('data_plan asc')->one();
        return isset($data) ? $data->data_plan : NULL;
    } else return NULL;
}

Of course I declared and used virtual attribute to gather data: $nextActionDate

The problem is sorting and filtering data.

Sorting

First try:

$query = KartaPrzyrzadu::find()->groupBy(['id_przyrz']);
        $query->joinWith('dzialania');


    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    $dataProvider->sort->attributes['nextActionDate'] = [
        // 1st try 'asc' => ['least(dzialania.data_plan, NOW() + interval co_zostalo day)' => SORT_ASC],
        // 1st try 'desc' => ['least(dzialania.data_plan, NOW() + interval co_zostalo day)' => SORT_DESC]
        // 2nd try 'asc' => [new \yii\db\Expression('CASE WHEN karta_przyrzadu.co_data = 0 THEN dzialania.data_plan END ASC, CASE WHEN co_data = 1 THEN NOW() + interval co_zostalo day END ASC')],
        // 2nd try 'desc' => [new \yii\db\Expression('CASE WHEN karta_przyrzadu.co_data = 0 THEN dzialania.data_plan END DESC, CASE WHEN co_data = 1 THEN NOW() + interval co_zostalo day END DESC')],
    ];

Buuut nope, tried to figure out why it doesn't work. And sql code of course.

Second try: by pure sql (haven't implemented it in code yet, I'm testing sql now) - and it is nearly what i need.

`SELECT id_przyrz ,MIN(data_plan) as data_plan FROM
(
(SELECT `karta_przyrzadu`.`id_przyrz`, NOW() + interval co_zostalo day as `data_plan` FROM `karta_przyrzadu` WHERE NOT (`karta_przyrzadu`.`status`='Wycofany') and co_data = 0)
UNION
(SELECT `dzialania`.`id_przyrz_d` AS `id_przyrz`, `data_plan` FROM `dzialania` where wyk ="Nie" order by data_plan ASC)
) 
`data_kart` group by id_przyrz ORDER BY `data_plan` DESC`

What i need is (i think) inner join it with rest of T1 table, but how? (I need all of data in grid, not only id_przyrz and data_plan)

Filtering

if (!is_null($this->nextActionDate) && strpos($this->nextActionDate, ' - ')     !== false ) {
        list($dateRangeStart, $dateRangeEnd) = explode(' - ', $this->nextActionDate);
            $query->andFilterWhere(['between', 'dzialania.data_plan', $dateRangeStart, $dateRangeEnd])
            ->orFilterWhere(['between', 'NOW() + interval co_zostalo day', $dateRangeStart, $dateRangeEnd]);
        }

I think there's enough data to understand the problem.

Thanks

Found proper SQL, now how to do it with AR? So I can properly sort...

SELECT * from (
SELECT id_przyrz, MIN(data_plan) as data_plan FROM
    (
        (SELECT `id_przyrz`, NOW() + interval co_zostalo day as `data_plan` FROM `karta_przyrzadu` WHERE NOT (`status`='Wycofany') and co_data = 0)
        UNION
        (SELECT `id_przyrz_d` AS `id_przyrz`, `data_plan` FROM `dzialania` where wyk ="Nie" order by data_plan ASC)
    ) as `table1` group by id_przyrz ORDER BY `data_plan` DESC
) as `full` left join karta_przyrzadu as full_on on full.id_przyrz = full_on.id_przyrz