如何使用php/mysql急切加载或类似方法优化mysql查询

Question

我有两张桌子。1- "cpv“；2- "club_offer_cpv”。

在表" CPV“中，我存储CPV代码、它们的ids和名称。您可以看到CPV代码这里。有6个级别的CPV代码，您可以通过访问提供的链接看到。

在CPV表中有字段："id“和"name”。

下面是几个一级CPV的例子(我们可以称之为根CPV )：

id: 03000000

名称:农业、农业、渔业、林业及相关产品

id: 09000000

名称:石油产品、燃料、电力和其他能源

有45个根CPV，他们有自己的孩子。从我提供给您的链接中可以看出，它们的I的层次结构非常严格。例如，根CPV 03000000有二级子级：

03100000 :农业和园艺产品

03200000 :谷类、土豆、蔬菜、水果和坚果

03300000 :农业、狩猎和渔业产品

03400000 :林业和伐木产品

现在，每个CPV都有在该领域开展业务的公司(公司提供该领域的报价)，例如，

03100000 :农业和园艺产品

很少有公司的业务是创造食品，这也是他们与cpv联系的原因。这些公司的报价存储在不同的表"company_offer“中，但这一点现在并不重要。重要的是有表"company_offer_cpv“连接company_offer和cpv表。

"company_offer_cpv“表具有以下字段：

"id"，"company_offer_id"，"cpv_id“。这就是我如何连接公司与cpv代码在他们的领域，因为他们可以有许多代码分配给他们。

我想要做的是对每个CPV组在company_offer_cpv表中使用的所有cpv进行计数，并显示组旁边的数字(当我说组时，我指的是根cpv)名称。就像这样：

03000000 :农业、农业、渔业、林业及相关产品:240个

09000000 :石油产品、燃料、电力和其他能源: 130

14000000 :采矿、基本金属及相关产品: 72

等等..。

我使用的是yii2 php框架，但是如果您不使用它并不重要，您可以帮助我编写mysql/php代码。

要计数表company_offer_cpv中使用的所有CPV的数量，并如上面所述显示它们，我将使用以下代码：

$total = ClubOfferCpv::find()->groupBy( ['LEFT(cpv_id, 2)'] )
                             ->select( ['LEFT(cpv_id, 2) AS cpv_id', 'COUNT(cpv_id) AS count'])
                             ->all();

foreach ($total as $data) 
{
    $id = $data->cpv_id."000000";

    $cpvName = Cpv::find()->select('name')->where(['id' => $id])->one();

    echo $cpvName->name. " " . $data->count;
    echo "<br>";
}

但是，我的代码中有一个缺陷(或更多:)。如果您查看foreach循环，我将执行这一行代码以获取所有根cpv名称，以便在计数数字旁边显示它们：

$cpvName =Cpv：：find()->select(‘name’)->where(id‘=> $id)->one()；

由于有45个根CPV，这一行将导致执行45个查询。我总共需要做1到2个查询，但我不知道怎么做。我知道Yii2正在提供急装，但我不知道如何使用它。有人知道我如何使用yii2或普通的sql/php来解决这个问题吗？

这是由第一行代码执行的sql：

SELECT LEFT(cpv_id, 2) AS cpv_id, COUNT(cpv_id) AS count FROM `club_offer_cpv` GROUP BY LEFT(cpv_id, 2)

这是foreach内部的行执行的SQL之一：

SELECT `name` FROM `cpv` WHERE `id`='50000000'

提前谢谢你

下面是对JC的帮助做出的一个很好的回复的更新：

如果我尝试像您所说的那样进行yii2的急切加载，我就不会得到任何结果，因为我需要从我的company_offer_cpv表中修剪cpv_id。以下是eager执行的查询：

SELECT LEFT(cpv_id, 2) AS cpv_id, COUNT(cpv_id) AS count FROM `club_offer_cpv` GROUP BY LEFT(cpv_id, 2) ;   

第二：

SELECT * FROM `cpv` WHERE `id` IN ('03', '09', '14', '15', '16', '18', '19', '22', '24', '30', '31', '32', '33', '34', '35', '37', '38', '39', '41', '42', '43', '44', '45', '48', '50', '51', '55', '60', '63', '64', '65', '66', '70', '71', '72', '73', '75', '76', '77', '79', '80', '85', '90', '92', '98')

非常感谢JC Sama！

以下是完整的解决方案：

$total = ClubOfferCpv::find()->groupBy( ['LEFT(cpv_id, 2)'] )
                             ->select( ['LEFT(cpv_id, 2) AS cpv', 'CONCAT(LEFT(cpv_id, 2), "000000") AS cpv_id', 'COUNT(cpv_id) AS count'])
                             ->with('cpv')
                             ->all();

foreach ($total as $data) 
{
    echo $data->cpv->name. " " . $data->count;
    echo "<br>";
}

Answer 1

我不熟悉Yii，但根据文档，您可以尝试这样的方法：

$total = ClubOfferCpv::find()->groupBy( ['LEFT(cpv_id, 2)'] )
                             ->select( ['LEFT(cpv_id, 2) AS cpv', 'CONCAT(LEFT(cpv_id, 2), "000000") AS cpv_id', 'COUNT(cpv_id) AS count'])
                             ->with('cpv')
                             ->all();

或者，您可以对第二个查询使用WHERE IN()状态，因为使用急切加载的方法是相同的：

$ids = array();
foreach ($total as $data) 
{
    $id[] = $data->cpv_id."000000";
}

然后：

// I'm not sure about WhereIn() Yii stuff :)
$cpvNames = Cpv::find()->select('name, id')->where(['id' => $ids])->all();
// Create an associative array, not sure how it's done with Yii
$cpvNames = CHtml::listData( $cpvNames, 'id' , 'name'); 

最后：

foreach ($total as $data) {

    $id = $data->cpv_id."000000";
    echo $cpvNames[$id]. " " . $data->count;
    echo "<br>";
}

重述:最后只有两个查询：

SELECT LEFT(cpv_id, 2) AS cpv_id, COUNT(cpv_id) AS count FROM `club_offer_cpv` GROUP BY LEFT(cpv_id, 2)

第二个使用where in而不是equal

SELECT `name` FROM `cpv` WHERE `id` in ('50000000', 800000, ...)