Как правильно выполнить группировку в ActiveRecord для запроса с несколькими LEFT JOIN?

[EVOSandru6]

Добрый день,

Есть такие модели со связями:

class Offers ( предложения )

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

public function getDeals()
    {
        return $this->hasMany(Deals::className(), ['offer_id' => 'id']);
    }
    public function getBrand()
    {
        return $this->hasOne(Brands::className(), ['id' => 'brand_id']);
    }
    public function getCategory()
    {
        return $this->hasOne(CategoryOffers::className(), ['id' => 'category_id']);
    }

class Deals (товары - по нескольку на одно предложение)

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

public function getCoupons()
    {
        return $this->hasMany(Coupons::className(), ['deal_id' => 'id']);
    }
    public function getOffer()
    {
        return $this->hasOne(Offers::className(), ['id' => 'offer_id']);
    }

class CategoryOffers ( категория предложения )

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

 public function getOffers()
    {
        return $this->hasMany(Offers::className(), ['category_id' => 'id']);
    }

class Coupons ( купоны - аналогия стандартного Orders - только 1купон - 1товар )

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

 public function getDeal()
    {
        return $this->hasOne(Deals::className(), ['id' => 'deal_id']);
    }

Мне нужно вытащить 5 брендов по самым продаваемым товарам текущей категории:

CategoryOffers::getHotBrands()

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.deal_id) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy('m_coupons.deal_id')
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->all();
}

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_brands.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "m_brands".*, count(m_coupons.*) as cnt FROM "m_brand...
^
The SQL being executed was: SELECT "m_brands".*, count(m_coupons.*) as cnt FROM "m_brands" LEFT JOIN "m_offers" ON "m_brands"."id" = "m_offers"."brand_id" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id" ORDER BY "cnt" DESC
Error Info: Array
(
[0] => 42803
[1] => 7
[2] => ERROR: column "m_brands.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "m_brands".*, count(m_coupons.*) as cnt FROM "m_brand...

Подскажите пожалуйста, что мне нужно изменить, чтобы заработало?

[zelenin]

Grouping error: 7 ERROR: column "m_brands.id" must appear in the GROUP BY clause or be used in an aggregate function

ну?

[EVOSandru6]

Grouping error: 7 ERROR: column "m_brands.id" must appear in the GROUP BY clause or be used in an aggregate function

ну?

Благодарю,

Пробую так, если я Вас правильно понял:

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.deal_id) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id'])

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (4, 2, 11, 15, 6, 10, 3, 1, 13, 5, 7, 16, 14, 9, 12, 8) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
Error Info: Array
(
[0] => 42803
[1] => 7
[2] => ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC

Если так:

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.deal_id) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id','m_offers.id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id'])

То:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (4, 2, 11, 15, 6, 10, 3, 1, 13, 5, 7, 16, 14, 9, 12, 8) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
Error Info: Array
(
[0] => 42803
[1] => 7
[2] => ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC

[zelenin]

все, что включаете в select, должно быть в groupBy

[EVOSandru6]

все, что включаете в select, должно быть в groupBy

Если я группирую по cnt таким образом:

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.deal_id) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id','m_offers.id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id','cnt'])

Ругается:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (2, 11, 4, 8, 5, 9, 6, 1, 15, 10, 7, 16, 3, 14, 13, 12) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC


Хотя в SELECT я пишу не

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

'count(m_coupons.deal_id) as cnt', 

а

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

 'count(m_coupons.deal_id) 'as cnt

Если же в groupBy указываю явно:

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.deal_id) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id','m_offers.id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id','m_coupons.cnt'])

То ловлю:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (2, 11, 4, 8, 5, 9, 6, 1, 15, 10, 7, 16, 3, 14, 13, 12) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC

[zelenin]

у вас такое выражение - count(m_coupons.deal_id)

[EVOSandru6]

у вас такое выражение - count(m_coupons.deal_id)

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.*) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id','m_offers.id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id', 'cnt'])

Если переделываю SELECT на
count(m_coupons.*) as cnt

Ошибка сохраняется:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (1, 7, 8, 15, 12, 4, 10, 16, 6, 3, 13, 9, 11, 14, 2, 5) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
Error Info: Array
(
[0] => 42803
[1] => 7
[2] => ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC

[zelenin]

m_deals.cnt включите в group by

[EVOSandru6]

m_deals.cnt включите в group by

Странно, не могу понять, при чем тут m_deals.cnt, если я он ни в одном селекте не фигурирует?!

Рассматривалось именно кол-во купонов с самыми продаваемыми товарами, т.е. группировка по ним.

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

$brands = Brands::find()->select(['m_brands.*','count(m_coupons.*) as cnt'])
            ->joinWith(['offers'=>function($q) {
                  $q->joinWith(['deals'=>function($q){
                    $q->joinWith(['coupons'=>function($q) {
                         $q
                            ->orderBy('cnt desc')
                             ->limit(5)
                            ->groupBy(['m_coupons.deal_id','m_offers.id'])
                            ;
                    }]);
                }])->groupBy(['m_offers.brand_id'])
                ;
            }])->groupBy(['m_brands.id', 'm_deals.cnt'])

Почему то ошибка все та же:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
^
The SQL being executed was: SELECT "m_offers".* FROM "m_offers" LEFT JOIN "m_deals" ON "m_offers"."id" = "m_deals"."offer_id" LEFT JOIN "m_coupons" ON "m_deals"."id" = "m_coupons"."deal_id" WHERE "m_offers"."brand_id" IN (1, 7, 8, 15, 12, 4, 10, 16, 6, 3, 13, 9, 11, 14, 2, 5) GROUP BY "m_offers"."brand_id", "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC
Error Info: Array
(
[0] => 42803
[1] => 7
[2] => ERROR: column "m_deals.cnt" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..., "m_coupons"."deal_id", "m_offers"."id" ORDER BY "cnt" DESC

[zelenin]

на sql запрос-то смотрите. там не корневой запрос, а один из джойновых

> Странно, не могу понять, при чем тут m_deals.cnt, если я он ни в одном селекте не фигурирует?!

потому что он в order by есть