ActiveQuery построение сложного запроса

[AndreyKolomoets]

Добрый день!
Нужна помощь в построение запроса ActiveQuery . Я уже 3 дня ломаю голову, но видимо моих знаний не достаточно.

Есть 2 таблицы:
- clients (
client_id,
client_title,
client_type
.....)

- client_details (
row_id,
detail_client_id,
detail_type,
detail_value,
....)

Связи:
public function getClientDetails()
{
return $this->hasMany(ClientDetail::class, ['detail_client_id' => 'client_id']);
}

public function getClient()
{
return $this->hasOne(Client::class, ['client_id' => 'detail_client_id']);
}

В таблице clients хранятся клиенты с различными типами (нас интересуют тип 5 и 6).
В таблице client_details хранятся характеристики этих клиентов (не ограниченное количество).

Связь между клиентами разных типов 5(юр. лица) и 6(договора) следующая.
У клиента с типом 5 (из таблици clients) есть записи (несколько) в таблице client_details где detail_type =111 а detail_value-ЭТО НОМЕР ДОГОВОРА.
У клиента с типом 6 (из таблици clients) есть одна запись в таблице client_details где detail_type =86 а detail_value-ЭТО НОМЕР ДОГОВОРА.

Как построить activerecord запрос в ClientSearch чтобы получить все записи из таблицы clients с типом 6 (с данными некоторых характеристик из таблицы client_details) и к ним притянуть client_title из таблицы clients с типом 5.
Т.е. у меня есть список договором, мне нужно вывести его в GridView с определенными характеристиками и названием юр. лица.

Сейчас есть такой запрос:
$query = LegalEntity::find()->alias('c')
->joinWith([
'clientDetails cd' => static function (ActiveQuery $query) {
$query->andOnCondition(['cd.detail_type' => DetailType::TYPE_LEGAL_ENTITY_ADDRESS]);
$query->andOnCondition(['cd.detail_status' => Detail::STATUS_ACTIVE]);
}
])
->andFilterWhere([
'c.client_type' => $this->validationRange
]);

$this->load($params);

$dataProvider = new ActiveDataProvider([
'query' => $query,
'pagination' => [
'pageSize' => 50
]
]);
Так получаю все заказы с характеристиками. А название юр. лица вытягиваю отдельным методом, непосредственно в GridView. Но это не позволяет фильтровать по юр. лицу и плодит много дополнительных запросов к БД.

[unknownby]

А зачем выводить условия для джоина clientDetails внутрь самого джоина?
Если по сути такая запись будет идентичной в данном случае

Код: Выделить всё

$query = LegalEntity::find()->alias('c')
->joinWith(['clientDetails cd', false, 'LEFT JOIN'])
->andWhere(['cd.detail_type' => DetailType::TYPE_LEGAL_ENTITY_ADDRESS])
->andWhere(['cd.detail_status' => Detail::STATUS_ACTIVE])
->andFilterWhere([
'c.client_type' => $this->validationRange
]);

Но предположительно не в этом вопрос)))
А в том, что написано в гриде (вьюхе), что в модели поиска и что в контроллере.
Из-за множества информации, не совсем понятно, что нужно в итоге.
Подправить запрос? Помочь фильтровать данные, которые приходят после запроса? Убрать множество каких-то запросов?

[AndreyKolomoets]

Есть 2 таблицы (расписаны в начале текста).
Хочу написать такой запрос для ActiveDataProvider чтобы на выходе получить таблицу Clients с полями:
client_id,
client_title,
client_type где client_type = 6 (это выполняется фильтром $this->validationRange),
+
client_title AS name из табл Clients где client_type = 5.

По связям описанным в тексте вопроса.
(
У клиента с типом 5 (из таблици clients) есть записи/строки (несколько) в таблице client_details где поле
detail_type = 111
detail_value = НОМЕР ДОГОВОРА (ключевая связь).

У клиента с типом 6 (из таблици clients) есть одна запись/строка в таблице client_details где поле
detail_type = 86
detail_value = НОМЕР ДОГОВОРА (ключевая связь).
)

[unknownby]

Есть 2 таблицы (расписаны в начале текста).
Хочу написать такой запрос для ActiveDataProvider чтобы на выходе получить таблицу Clients с полями:
client_id,
client_title,
client_type где client_type = 6 (это выполняется фильтром $this->validationRange),
+
client_title AS name из табл Clients где client_type = 5.

По связям описанным в тексте вопроса.
(
У клиента с типом 5 (из таблици clients) есть записи/строки (несколько) в таблице client_details где поле
detail_type = 111
detail_value = НОМЕР ДОГОВОРА (ключевая связь).

У клиента с типом 6 (из таблици clients) есть одна запись/строка в таблице client_details где поле
detail_type = 86
detail_value = НОМЕР ДОГОВОРА (ключевая связь).
)

Сперва нужно не ActiveQuery сделать, а запрос на SQL, а потом уже перевести его в ActiveQuery. Предлагаю вам сделать вначале SQL-запрос, а там посмотрим, что вышло.

[AndreyKolomoets]

Вот как выглядит запрос на SQL

Код: Выделить всё

SELECT clients.client_id,
       clients.client_title,
       clients.client_type,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = 41
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
       LIMIT 1
       ) AS address,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = 9
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
        ORDER BY cd.row_id DESC
        LIMIT 1
       ) AS fias,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = (CASE
                                    WHEN client_type = 6
                                        THEN 6
                                    WHEN client_type = 7
                                        THEN 86
                                END)
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
        LIMIT 1
       ) AS bgb,

       (SELECT cl.client_title
           FROM clients cl
                    JOIN client_details cd ON cd.detail_client_id = cl.client_id
           WHERE
                   cd.detail_type = 111 AND
                   cd.detail_value = bgb

       GROUP BY cl.client_title
       LIMIT 1
       ) AS name

FROM clients
LEFT JOIN client_details ON client_details.detail_client_id = clients.client_id
WHERE
        (clients.client_type = 6 OR clients.client_type = 7)
        AND
        clients.client_status = 1
GROUP BY clients.client_title

[unknownby]

Вот как выглядит запрос на SQL

Код: Выделить всё

SELECT clients.client_id,
       clients.client_title,
       clients.client_type,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = 41
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
       LIMIT 1
       ) AS address,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = 9
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
        ORDER BY cd.row_id DESC
        LIMIT 1
       ) AS fias,
       (select cd.detail_value
        from  client_details cd
        where cd.detail_type = (CASE
                                    WHEN client_type = 6
                                        THEN 6
                                    WHEN client_type = 7
                                        THEN 86
                                END)
          AND cd.detail_client_id = client_id
          AND cd.detail_status = 1
        LIMIT 1
       ) AS bgb,

       (SELECT cl.client_title
           FROM clients cl
                    JOIN client_details cd ON cd.detail_client_id = cl.client_id
           WHERE
                   cd.detail_type = 111 AND
                   cd.detail_value = bgb

       GROUP BY cl.client_title
       LIMIT 1
       ) AS name

FROM clients
LEFT JOIN client_details ON client_details.detail_client_id = clients.client_id
WHERE
        (clients.client_type = 6 OR clients.client_type = 7)
        AND
        clients.client_status = 1
GROUP BY clients.client_title

Начнем по порядку.
Основной запрос

Код: Выделить всё

$query = LegalEntity::find()->alias('c')
            ->select(['c.client_id', 'c.client_title', 'c.client_type'])
            ->andWhere(['or',
            	['c.client_type' => 6],
            	['c.client_type' => 7],
            ])
            ->andWhere(['c.client_status' => 1])
            ->groupBy('c.client_title');

Три подзапроса

Код: Выделить всё

//address
$subQuery1 = ClientDetails::find()->alias('cda')
	->select(['cda.detail_value'])
	->where(['and',
		['cda.detail_type' => 41],
		['cda.detail_client_id ' => 'c.client_id'],
		['cda.detail_status' => 1],
	]);
	//->limit(1);

//fias
$subQuery2 = ClientDetails::find()->alias('cdb')
	->select(['cdb.detail_value'])
	->where(['and',
		['cdb.detail_type' => 9],
		['cdb.detail_client_id ' => 'c.client_id'],
		['cdb.detail_status' => 1],
	])
	->orderBy(['cdb.row_id' => SORT_DESC]);
	//->limit(1);

//bgb
$subQuery3 = ClientDetails::find()->alias('cdc')
	->select(['cdc.detail_value'])
	->where(['and',
		['cdc.detail_type' => 'CASE WHEN c.client_type = 6 THEN 6 WHEN c.client_type = 7 THEN 86 END'],
		['cdc.detail_client_id ' => 'c.client_id'],
		['cdc.detail_status' => 1],
	]);
	//->limit(1);

//name
$subQuery4 = LegalEntity::find()->alias('cl')
	->select(['cl.client_title'])
	->joinWith(['clientDetails cdd', false, 'LEFT JOIN'])
	->where(['and',
		['cdd.detail_type' => 111],
		['cdd.detail_value' => 'bgb'],
	])
	->groupBy('cl.client_title');
	//->limit(1);

А теперь объединим получившееся

Код: Выделить всё

$query = LegalEntity::find()->alias('c')
            ->select(['c.client_id', 'c.client_title', 'c.client_type',
            	'address' => $subQuery1,
            	'fias' => $subQuery2,
            	'bgb' => $subQuery3,
            	'name' => $subQuery4,
            ])
            ->andWhere(['or',
            	['c.client_type' => 6],
            	['c.client_type' => 7],
            ])
            ->andWhere(['c.client_status' => 1])
            ->groupBy('c.client_title');

Вариант конечно с составлением такого большого запроса под сомнением
Наверняка есть более изящное решение для вашей простой задачи
Оставил возможность добавления лимита при тестировании. Лимит 1 можно было бы через связи сделать hasOne, не используя limit(1). А все andWhere заменить на andOnCondition.
Было бы просто штуки три-четыре джойна в запросе.

[AndreyKolomoets]

Попробовал Ваш код.
Выдает ошибку:

Invalid Argument – yii\base\InvalidArgumentException
common\models\client\LegalEntity has no relation named "".
↵
Caused by: Unknown Method – yii\base\UnknownMethodException
Calling unknown method: common\models\client\LegalEntity::get()

Скорее всего это связано с кодом подзапроса:

Код: Выделить всё

 $subQuery4 = LegalEntity::find()->alias('cl')
            ->select(['cl.client_title'])
            ->joinWith(['clientDetails cdd', false, 'LEFT JOIN'])
            ->where(['and',
                ['cdd.detail_type' => 111],
                ['cdd.detail_value' => 'bgb'],
            ])
            ->groupBy('cl.client_title');
        //->limit(1);

.

Т.к. он обращается к той-же таблице что и основной запрос:

Код: Выделить всё

 $query = LegalEntity::find()->alias('c')
            ->select(['c.client_id', 'c.client_title', 'c.client_type',
                'address' => $subQuery1,
                'fias' => $subQuery2,
                'bgb' => $subQuery3,
                'name' => $subQuery4,
            ])
            ->andWhere(['or',
                ['c.client_type' => 6],
                ['c.client_type' => 7],
            ])
            ->andWhere(['c.client_status' => 1])
            ->groupBy('c.client_title')->asArray();

Не подскажите какое может быть решение?
В любом случае, спасибо за ответ!!!

[unknownby]

Попробовал Ваш код.
Выдает ошибку:

Invalid Argument – yii\base\InvalidArgumentException
common\models\client\LegalEntity has no relation named "".
↵
Caused by: Unknown Method – yii\base\UnknownMethodException
Calling unknown method: common\models\client\LegalEntity::get()

Не подскажите какое может быть решение?
В любом случае, спасибо за ответ!!!

Пишет, что в модели LegalEntity нет связи с ClientDetails через

Код: Выделить всё

public function getClientDetails()
{
    return $this->hasMany(ClientDetails::className(), ['detail_client_id' => 'client_id']);
}

Можно проверить еще без подзапроса 4го, будет ли выдавать хоть что-то запрос. Может не в четвертом подзапросе дело

[AndreyKolomoets]

Без 4-го запроса:

Код: Выделить всё

Database Exception – yii\db\Exception
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cda.detail_client_id ' in 'where clause'
The SQL being executed was: SELECT COUNT(*) FROM (SELECT `c`.`client_id`, `c`.`client_title`, `c`.`client_type`, (SELECT `cda`.`detail_value` FROM `client_details` `cda` WHERE (`cda`.`detail_type`=41) AND (`cda`.`detail_client_id `='c.client_id') AND (`cda`.`detail_status`=1)) AS `address`, (SELECT `cdb`.`detail_value` FROM `client_details` `cdb` WHERE (`cdb`.`detail_type`=9) AND (`cdb`.`detail_client_id `='c.client_id') AND (`cdb`.`detail_status`=1) ORDER BY `cdb`.`row_id` DESC) AS `fias`, (SELECT `cdc`.`detail_value` FROM `client_details` `cdc` WHERE (`cdc`.`detail_type`='CASE WHEN c.client_type = 6 THEN 6 WHEN c.client_type = 7 THEN 86 END') AND (`cdc`.`detail_client_id `='c.client_id') AND (`cdc`.`detail_status`=1)) AS `bgb` FROM `clients` `c` WHERE ((`c`.`client_type`=6) OR (`c`.`client_type`=7)) AND (`c`.`client_status`=1) GROUP BY `c`.`client_title`) `c`

Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'cda.detail_client_id ' in 'where clause'
)

↵
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cda.detail_client_id ' in 'where clause'

Почему-то не видит подзапросы. Или подзапросы не видят таблицу 'c' из основного запроса.

[unknownby]

Замени строки где есть пробел

Код: Выделить всё

['cdc.detail_client_id ' => 'c.client_id'],

на строки без пробела

Код: Выделить всё

['cdc.detail_client_id' => 'c.client_id'],

Я писал этот запрос тут, а не в IDE Ошибки с пробелами лишними мог случайно оставить
Все равно думаю, что слишком усложнена поставленная задача. Решается 99,99% намного проще, чем четыре подзапроса

Сможете ли сформулировать задачу без определения столбцов и т.п., например, у меня есть список клиентов, у каждого клиента есть что-то, нужно вытянуть то, то и то.
Просто судя по подзапросам, есть клиенты, у них есть "адрес", при условии что в деталях будет какой-то идентификатор и статус 1, у них есть "fias", при условии что в деталях будет какой-то идентификатор и статус 1, у них есть "bgb", с такой же ерундой и "name" в зависимости от "bgb". Слишком уж запутанно

[AndreyKolomoets]

По пробую сформулировать задачу своими словами.

Есть таблица Клиенты, в которой хранятся названия Клиентов (юр. лица), Договоров этих клиентов и Заказов. Отличаются по типу клиента.
В таблице Детали хранятся характеристики клиентов/договоров/заказов.

Мне нужно вывести в одном столбце названия Договоров и Заказов (из табл Клиенты), во втором их адреса (из табл Детали),
а в третьем названия Юр лица (из табл Клиенты), к которому привязаны данные Договора.
Заказы привязаны к Юр лицу через Договора.

Сложность в связях. все нижеописанное в таблице Детали.
Если это Заказ, у него есть запись с типом 86, которая указывает на КОД ДОГОВОРА.
Если это Договор, у него есть запись с типом 6, которая указывает на КОД ДОГОВОРА.
У клиента есть множество записей с типом 111, которые указывают на КОДЫ ДОГОВОРА, подчиненных ему

[unknownby]

Сложность в связях. все нижеописанное в таблице Детали.
Если это Заказ, у него есть запись с типом 86, которая указывает на КОД ДОГОВОРА.
Если это Договор, у него есть запись с типом 6, которая указывает на КОД ДОГОВОРА.
У клиента есть множество записей с типом 111, которые указывают на КОДЫ ДОГОВОРА, подчиненных ему

Если я правильно понял, то в таблице деталей хранится информация о заказах и договорах, различается только "типом".
Проще было сделать две разные таблицы, где:

Таблица с заказами
orders_id, client_id, orders_number
Таблица с договорами
contracts_id, client_id, contracts_number

Через две связи hasMany() реализовать получение сведений из данных таблиц, в вашем случае можно сделать две связи hasMany() и задать по-умолчанию тип=86, статус=1 (статусы нужно или нет, решать вам, если они есть в таблице деталей).
Ну и логично задать для договора тип=6, статус=1.

Код: Выделить всё

public function getClientDetailsOrders() {
        return $this->hasMany(ClientDetails::className(), ['detail_client_id' => 'client_id'])
        ->onCondition(['detail_type' => 86])
        ->andOnCondition(['detail_status' => 1]);
    }
    
public function getClientDetailsContracts() {
        return $this->hasMany(ClientDetails::className(), ['detail_client_id' => 'client_id'])
        ->onCondition(['detail_type' => 6])
        ->andOnCondition(['detail_status' => 1]);
    }

Можно сделать hasOne, задать сортировку обратную и доставать только последний заказ/договор.
Чем хорошо делать связи? Тем, что можно будет их использовать не один раз, а если писать запросы, то будет множественное дублирование кода.
Чтоб не было проблем, можно (скорее нужно) указать alias() в связях, ну или всегда в запросах указывать алиасы
как тут, указываем алиас cdd

Код: Выделить всё

->joinWith(['clientDetails cdd', false, 'LEFT JOIN'])

А вот насчет тип=111 не понятно, для чего это вообще надо? Связать договора и заказы?
Для этого нужна смежная таблица в таком случае

P.S.
После использования связей, достаточно было бы вытянуть в select "алиас.наименование_поля" из нужной связи.

[AndreyKolomoets]

Я с Вами полностью согласен.
Но я пришел в рабочий проект, и приходится работать с тем что есть.

[unknownby]

Я с Вами полностью согласен.
Но я пришел в рабочий проект, и приходится работать с тем что есть.

В рабочий проект можно добавить связи и при помощи связей уже искать то, что нужно.
А что касается тип=111, что это такое?

[AndreyKolomoets]

Тип 111.
У Юр лица (тип 5 из таблицы Клиенты) в таблице Детали хранятся множество или одна записи с типом 111.
Значения этих записей - это код договора.
По этим данным и происходит связь в таблицах.
Я так думал.
Как сегодня выяснилось в тестовой базе есть ошибка - информация о ИНН должна была храниться в таблице Детали как у Юр лица, так и у Заказа и у Договора.
По значению ИНН и строилась связь.

Тем не менее с вашей помощью мне удалось построить запрос по описанным ранее условиям.

Код: Выделить всё

     $query = LegalEntity::find()->alias('c')
            ->select(['c.client_id', 'c.client_title', 'c.client_type',
                'address' => 'cda.detail_value',
                'fias' => 'cdf.detail_value',
                'bgb' => 'cdb.detail_value',
                'nameid' => 'cdcl.detail_client_id',
                'name' => 'cl.client_title',
            ])
            ->leftJoin(['cda' => 'client_details'],
                [
                    'and',
                    ['cda.detail_client_id' => new Expression('c.client_id')],
                    ['cda.detail_type' => DetailType::TYPE_LEGAL_ENTITY_ADDRESS],
                    ['cda.detail_status' => Detail::STATUS_ACTIVE],
                ]
            )
            ->leftJoin(['cdf' => 'client_details'],
                [
                    'and',
                    ['cdf.detail_client_id' => new Expression('c.client_id')],
                    ['cdf.detail_type' => DetailType::TYPE_FIAS],
                    ['cdf.detail_status' => Detail::STATUS_ACTIVE],
                ]
            )
            ->leftJoin(['cdb' => 'client_details'],
                [
                    'and',
                    ['cdb.detail_client_id' => new Expression('c.client_id')],
                    ['cdb.detail_type' => new Expression('CASE WHEN c.client_type = 6 THEN 6 WHEN c.client_type = 7 THEN 86 END')],
                    ['cdb.detail_status' => Detail::STATUS_ACTIVE],
                ]
            )

            ->leftJoin(['cdcl' => 'client_details'],
                [
                    'and',
                    ['cdcl.detail_type' => DetailType::TYPE_SUBCONTRACT],
                    ['cdcl.detail_value' => new Expression('cdb.detail_value')],
                    ['cdb.detail_status' => Detail::STATUS_ACTIVE],
                ]
            )
            ->leftJoin(['cl' => 'clients'],
                [
                    'and',
                    ['cl.client_type' =>  ClientTypeList::TYPE_LEGAL_ENTITIES],
                    ['cl.client_id' => new Expression('cdcl.detail_client_id')],
                    ['cl.client_status' => Detail::STATUS_ACTIVE],
                ]
            )
            ->andWhere(['c.client_status' => Detail::STATUS_ACTIVE])
            ->andFilterWhere([
                'c.client_type' => $this->validationRange
            ]);

где

Код: Выделить всё

DetailType::TYPE_LEGAL_ENTITY_ADDRESS = 41
DetailType::TYPE_FIAS = 9
DetailType::TYPE_SUBCONTRACT = 111
Detail::STATUS_ACTIVE = 1

$this->validationRange = [6, 7]

Может кому-то пригодится.