php - MySQL从多个表中复制SELECT中的数据

Question

我有 6 个表，根据以下结构 我正在使用的查询

SELECT 
    campaigns.idCampaign,

    users.idUser AS idUser,
    users.identification AS userIdentification,
    users.name AS userName,
    campaign_users.commission AS userCommission,
    campaign_files.idFile AS idFile,
    campaign_files.name AS fileName,
    clients.identification AS clientIdentification,
    suppliers.identification AS supplierIdentification
FROM
    campaigns
    LEFT JOIN campaign_users ON( campaigns.idCampaign = campaign_users.idCampaign)
    LEFT JOIN users ON( users.idUser = campaign_users.idUser )
    LEFT JOIN clients USING( idClient )
    LEFT JOIN suppliers ON ( suppliers.idSupplier = campaigns.idSupplier )
    LEFT JOIN campaign_files ON( campaigns.idCampaign = campaign_files.idCampaign)

根据 campaign_files 中的文件数量或 campaign_users 中的用户数量(较大者)，这会导致重复的营销事件。

这就是结果，正如您所看到的，idCampaign 是相同的，但多次，我希望将其放在单个对象中，如下所示

{

    idCampaign: 4,
    users: [
        {
            idUser: 1,
            userName: 'ADMIN' 
        },
        {
            idUser: 2,
            userName: 'Serena Huel'
        }
    ],
    files: [
        {
            idFile: 23,
            fileName: 'dshds9agds86das8gads8g5dsa.hal'
        },
        {
            idFile: 49,
            fileName: 'dshds9agds86das8gads8g5dsa.hal'
        }
        {
            idFile: 84,
            fileName: 'dshds9agds86das8gads8g5dsa.hal'
        },
        {
            idFile: 99,
            fileName: 'dshds9agds86das8gads8g5dsa.hal'
        }
    ],
    clientIdentification: "dolore",
    ...
}

我已添加输出作为示例，但它可以是 PHP 对象、数组等。

Answer 1

遗憾的是，这不是 MySQL 的工作方式。您可以执行 GROUP BY 和类似 GROUP_CONCAT 的操作，但这会给您留下字符串而不是数组。那么为什么不把它变成 PHP 中想要的对象呢？

假设查询可以返回多个campaignId，你可以这样做

$campaigns = array();

while ($row = /* fetch row */) {
    if (!isSet($campaigns[ $row['campaignId'] ])) {
        //new campaignId
        $campaigns[ $row['campaignId'] ] = array(
            'users' => array(),
            'files' => array(),
            'clientIdentification' => $row['clientIdentification']
        );
    }

    if (!isSet($campaigns[ $row['campaignId'] ]['users'][ $row['idUser'] ])) {
        //new user
        $campaigns[ $row['campaignId'] ]['users'][ $row['idUser'] ] = $row['userName'];
    }

    $campaigns[ $row['campaignId'] ]['files'][] = array(
        'idFile' => $row['idFile'],
        'fileName' => $row['fileName']
    );
}

这将为您提供一个数组 $campaigns ，这几乎就是您想要的。使用 id 作为键保存用户是一种避免重复用户的简单方法。现在要获得预期的对象，您可以执行类似的操作

foreach ($campaigns as $c) {
    $expectedObj = array(
        'idCampaign' => $c['idCampaign'],
        'users' => array(),
        'files' => $c['files'],
        'clientIdentification' => $c['clientIdentification']
    );
    foreach ($c['users'] as $idUser => $userName) {
        $expectedObj['users'][] = array(
            'idUser' => $idUser,
            'userName' => $userName
        );
    }
    // use $expectedObj
}