How to correctly select many_many in Yii2?

Question

MySQL

There are tables
products [id, name, publish, ....]
category [id, name, parent_id...] products_to_category
[product_id, category_id]
AUDI, OPEL, (the main category for all auto parts) then there will be 3 products in the Auto parts category, the
selection is done like this:
An excerpt from ProductsSearch.php search method

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

relation in Products.php

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

relation in Category.php

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

here is the query output

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)

ps in the ListView one product is displayed, that is, without duplicates, but in the summary it says that 3 products were found
pps Is there a way to bypass group by in such cases? since with large database volumes it will take a long time to execute queries from 30-40 seconds to 200 under loads

Answer 1

This is a problem of logic. It is necessary to rewrite the method that counts. You are not alone, the regular method does not work correctly with such samples.