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

I have 6 tables, according to following structure the query i am using

我有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)

This results in duplicate campaigns, according to the number of files in campaign_files or users in campaign_users (which is greater).

根据campaign_files中的文件数量或campaign_users中的用户数量(更高),这会导致重复的广告系列。

this is the result, as you can see the idCampaign is same but multiple times, i want to this in single object, like this

这是结果,因为你可以看到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",
    ...
}

I have added the output as example but it can be PHP object, array etc.

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

1 个解决方案

#1

Sadly, this is not how MySQL works. You could do a GROUP BY and something like GROUP_CONCAT, but this would leave you with strings and not arrays. So why not turn it into the wanted object in PHP?

可悲的是,这不是MySQL的工作原理。你可以做一个GROUP BY和类似GROUP_CONCAT的东西,但这会留下你的字符串,而不是数组。那么为什么不把它变成PHP中的想要的对象呢?

Assuming the query can return multiple campaignIds, you could do it like this

假设查询可以返回多个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']
    );
}

This will give you an array $campaigns which is nearly what you want. Saving the users using the id as key is a simple method to not have any duplicate users. Now to get the expected object, you can do something like

这将为您提供一个阵列$广告,几乎是您想要的。使用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
}