Table output based on SQL query.


(KevinSmash) #1

Hello. There is a main table with information about cities, population, etc. (Cities) There is also a second table with a list of stores, the category in which they work, etc. (Shop). The task is to bring into GridView one table that will be built on a SQL query, namely: output the number of competitors in a certain category, for one city. Such request turned out:

SELECT `Cities`.*,
(SELECT COUNT(*) FROM `avito_parse_shops` WHERE `Cities`.`city`=`avito_parse_shops`.`city` AND `category`="Товары для детей и игрушки" AND `shop_relevance`=0) AS comp_toy,
(SELECT COUNT(*) FROM `avito_parse_shops` WHERE `Cities`.`city`=`avito_parse_shops`.`city` AND `category`="Спорт и отдых" AND `shop_relevance`=0) AS comp_sport,
(SELECT COUNT(*) FROM `avito_parse_shops` WHERE `Cities`.`city`=`avito_parse_shops`.`city` AND `category`="Посуда и товары для кухни" AND `shop_relevance`=0) AS comp_kitchen
FROM `Cities` WHERE 1

I do not understand how to display such a large query in GridView


(Alexander Makarov) #2

How about using https://www.yiiframework.com/doc/api/2.0/yii-data-sqldataprovider ?