yii的多表查询

获取用户发布消息的指定消息id的总和点赞数

Yii

   $productIds = ['2260', '2262', '2263', '2268', '2269'];

   $plSql = Like::find()->where([

   'pId' => $pIds,

            'isLike' => 1

   ])->select('pId,count(id) c')

   ->groupBy('pId')->createCommand()->getRawSql();

    $messages = Message::find()

            ->innerJoin("({$plSql} ) as pl", "msg.id = pl.pId")

            ->alias('msg')->groupBy('msg.customerId')

            ->select('msg.customerId,SUM(pl.c) as s')

            ->createCommand()->getRawSql();

MySQL

SELECT

    `msg`.`customerId`,

    SUM(pl.c) AS s

FROM

    `message` `msg`

INNER JOIN (

    SELECT

        `pId`,

        count(id) c

    FROM

        `like`

    WHERE

        (

            `pId` IN (

                '',

                '',

                '',

                '',

                ''

            )

        )

    AND (`isLike` = 1)

    GROUP BY

        `pId`

) AS pl ON msg.id = pl.customerId

GROUP BY

    `msg`.`customerId`

巴特西

yii的多表查询

最新文章

热门文章