Как правильно сделать выборку many_many в Yii2?

Общие вопросы по использованию второй версии фреймворка. Если не знаете как что-то сделать и это про Yii 2, вам сюда.
Ответить
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

Есть таблицы
products [id, name,publish, ....]
category [id, name, parent_id...]
products_to_category [product_id, category_id]
При расчете количества товаров у главной категории выводятся дубли, допустим если один товар в подкатегориях BMW, AUDI, OPEL, (главная категории у всех автозапчасти) то будет считаться 3 товара у категории Автозапчасти

выборка делается так:

Отрывок из ProductsSearch.php метод search

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

$query = Products::find();
....
$query->joinWith('categories');
$query->andFilterWhere(['category.parent_id' => $this->mainCategoryID]);

relation в Products.php

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

public function getCategories() {
        return $this->hasMany(Category::className(), ['id' => 'category_id'])
            ->viaTable('products_to_category', ['product_id' => 'id']);
}


relation в Category.php

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

public function getParent()
{
        return $this->hasOne(Category::className(), ['id' => 'parent_id']);
}


вот вывод запроса

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

SELECT COUNT(*) FROM `products` LEFT JOIN `products_to_category` ON `products`.`id` = `products_to_category`.`product_id` LEFT JOIN `category` ON `products_to_category`.`category_id` = `category`.`id` WHERE (`category`.`parent_id`=1) AND (`publish`=1)


p.s в ListView выводится один товар, то есть без дублей, но в summary пишет, что найдено 3 товара

p.p.s Есть ли способ обойти group by в таких случаях? так как при больших объемах базы с ним будет долго выполняться запросы от 30-40 секунд до 200 при нагрузках
BalykhinAS
Сообщения: 179
Зарегистрирован: 2018.02.05, 13:41
Контактная информация:

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение BalykhinAS »

lolka писал(а): 2018.03.05, 09:04

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

SELECT COUNT(*) FROM `products` LEFT JOIN `products_to_category` ON `products`.`id` = `products_to_category`.`product_id` LEFT JOIN `category` ON `products_to_category`.`category_id` = `category`.`id` WHERE (`category`.`parent_id`=1) AND (`publish`=1)
адский запрос к базе :)

а в вашем каталоге всего 2 уровня?
в любом случае запрос должен выглядеть как то так

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

SELECT p.* FROM products WHERE id in(SELETC product_id FROM products_to_category WHERE category_id in (1, 2, 3, ...))
попробуйдет сделать по примеру viewtopic.php?f=19&t=46669#p232811 по ссылке правда нет промежуточной таблицы но смысл то же
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

Wizard писал(а): 2018.03.05, 09:35
адский запрос к базе :)
ну так это yii2 так формирует, я же через активрекорд делаю)
Wizard писал(а): 2018.03.05, 09:35 а в вашем каталоге всего 2 уровня?
да
BalykhinAS
Сообщения: 179
Зарегистрирован: 2018.02.05, 13:41
Контактная информация:

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение BalykhinAS »

lolka писал(а): 2018.03.05, 09:53
Wizard писал(а): 2018.03.05, 09:35
адский запрос к базе :)
ну так это yii2 так формирует, я же через активрекорд делаю)
Wizard писал(а): 2018.03.05, 09:35 а в вашем каталоге всего 2 уровня?
да
нет, так запрос формируете вы а yii его исполняет. Вы для чего то join-ите к таблице товаров таблицы категорий

максимум можно так, при количестве товаров до 20-30к (возможно и больше надо смотреть многие cms заявляют 100к) заметных тормозов точно не будет. а вообще все запросы имеющие промежуточные таблицы более нагруженный и от этого не избавится

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

INNER JOIN products_to_category pc ON pc.product_id = p.id AND pc.category_id in(1, 2, 3, ...)
двумя уровнями не стоит ограничиваться даже если на данный момент вы не предполагаете использовать больше
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

Wizard писал(а): 2018.03.05, 09:35 в любом случае запрос должен выглядеть как то так

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

SELECT p.* FROM products WHERE id in(SELETC product_id FROM products_to_category WHERE category_id in (1, 2, 3, ...))
кстати насчет подзапроса, а что если у этих категорий миллион товаров, тогда этот вариант не подходит
BalykhinAS
Сообщения: 179
Зарегистрирован: 2018.02.05, 13:41
Контактная информация:

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение BalykhinAS »

lolka писал(а): 2018.03.05, 11:10
Wizard писал(а): 2018.03.05, 09:35 в любом случае запрос должен выглядеть как то так

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

SELECT p.* FROM products WHERE id in(SELETC product_id FROM products_to_category WHERE category_id in (1, 2, 3, ...))
кстати насчет подзапроса, а что если у этих категорий миллион товаров, тогда этот вариант не подходит
так вы ведь весь миллион выбирать не будте, вы ведь предполагаете ограничить по лимиту? вариантов выборки товаров из базы по id категорий не много все они есть в данной ветке :)
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

с ограничением будет так
SELECT p.* FROM products WHERE id in(SELETC product_id FROM products_to_category WHERE category_id in (1, 2, 3, ...)) ORDER BY date DESC LIMIT 30

а в подзапросе его не будет, так как с ним будет левая сортировка
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

Wizard писал(а): 2018.03.05, 19:51 Используйте inner join
через relation не получится то есть по нормальному сделать в модели?
BalykhinAS
Сообщения: 179
Зарегистрирован: 2018.02.05, 13:41
Контактная информация:

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение BalykhinAS »

Relation это связь а не фильтр, потому использовать inner в качестве фильтра это более чем и нормально
lolka
Сообщения: 143
Зарегистрирован: 2013.05.05, 20:59

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение lolka »

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

$ids = [];
            foreach (Category::find()->select('id')->andWhere(['parent_id' => $this->mainCategoryID])->all() as $category) {
                $ids[] = $category->id;
            }
            $query->innerJoinWith('categories');
            $query->andFilterWhere(['category.id' => $ids]);
если так сделать, то результат тот же
BalykhinAS
Сообщения: 179
Зарегистрирован: 2018.02.05, 13:41
Контактная информация:

Re: Как правильно сделать выборку many_many в Yii2?

Сообщение BalykhinAS »

в вашем случае получается что то подобное

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

SELECT `p`.* FROM `products` `p` INNER JOIN `products_categories` ON `p`.`id` = `products_categories`.`product_id` INNER JOIN `categories` `c` ON `products_categories`.`category_id` = `c`.`id` WHERE `c`.`id` IN (2, '3', '4') GROUP BY `p`.`id`
из чего следуете что INNER JOIN `categories` для фильтра это лишний запрос, потому делаем как то так:

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

public function getCategoryAssignments()
{
    return $this->hasMany(CategoryAssignments::class, ['product_id' => 'id']);
}

public function getCategories()
{
    return $this->hasMany(Category::class, ['id' => 'category_id'])->via('categoryAssignments');
}

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

class CategoryAssignments extends ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return '{{%products_categories}}';
    }
}

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

$category = Category::findOne($id);

$query = Product::find()->alias('p');

if (!empty($category)) {
    $ids = [$category->id];
    $childrenIds = $ids;
    while ($childrenIds = Category::find()->select('id')->andWhere(['parent_id' => $childrenIds])->column()) {
        $ids = array_merge($ids, $childrenIds);
    }
    $query->innerJoinWith(['categoryAssignments pc'], false);
    $query->andWhere(['pc.category_id' => array_unique($ids)]);
    $query->groupBy('p.id');
}
получаем запрос такого вида

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

SELECT `p`.* FROM `s_products` `p` INNER JOIN `s_products_categories` `pc` ON `p`.`id` = `pc`.`product_id` WHERE `pc`.`category_id` IN (2, '3', '4') GROUP BY `p`.`id`
и вот уже yii научился делать человеческие запросы :) что собственно удовлетворяет наши потребности :) обязательно проверьте индексы в вашей базе, миллион товар конечно это много но до 100к такой запрос должен держать без особых тормозов. Все что с выше то к примеру elasticsearch вам в помощь или любые другие танцы с бубном
Ответить