Help with LeftJoin and grouping results from joined table

2 posts by 2 authors in: Forums > CMS Builder
Last Post: January 4, 2022   (RSS)

By JeffC - November 21, 2021 - edited: November 21, 2021

Hi

I have a table called 'campaign_item'
I have a related table called 'campaign_item_status' that contains a dropdown field for 'title' and field called 'campaign_itemNum' to link it to the campaign_item. 
A campaign_item can have several statuses.

The output I have is this:

Campaign Item 1       |      Status 1
Campaign Item 1       |      Status 2
Campaign Item 1       |      Status 3
Campaign Item 2       |     
Campaign Item 3       |      Status 1
Campaign Item 3       |      Status 2

The output I would like is this:

Campaign Item 1       |      Status 1, Status 2, Status 3
Campaign Item 2       |     
Campaign Item 3       |      Status 1, Status 2   

In my header I have this:

// load record from 'campaign_item'
list($campaign_itemRecords, $campaign_itemMetaData) = getRecords(array(
'tableName' => 'campaign_item',
'loadUploads' => false,
'allowSearch' => false,	
 'leftJoin' => array(
 'campaign_item_status' => 'ON campaign_item.num = campaign_item_status.campaign_itemNum',	  
 )
));

And in my body I have this:

<?php foreach ($campaign_itemRecords as $record): ?>
<tr>
<td><?php echo htmlencode($record['title']) ?><td>
<td><?php echo htmlencode($record['campaign_item_status.title']) ?><td>
</tr>
<?php endforeach ?>

I think I might be on the right track, and have managed to group the statuses in the developer console with MySql. My code is below. But 12 hours in, and I can't join the pieces together.  

SELECT cms_campaign_item_status.title, GROUP_CONCAT(title) title
FROM cms_campaign_item_status
GROUP BY cms_campaign_item_status.campaign_itemNum
Jeff

By daniel - January 4, 2022

Hi Jeff,

This might be a scenario where the CMSB function array_groupBy() can help out. It can be used like this:

$itemsByTitle = array_groupBy($campaign_itemRecords, 'title', true);
foreach ($itemsByTitle as $title => $groupedRecords) {
  echo 'Title: ' . $title;
  echo '<br>';

  foreach ($groupedRecords as $groupedRecord) {
    echo $groupedRecord['campaign_item_status.title'];
    echo '<br>';
  }
}

$itemsByTitle will be an array where the key is the 'title' field, and the values are an array of all records with that title. So in the top-level array $title should be "Campaign Item 1", and then $groupedRecords should be all of the records returned with the title "Campaign Item 1" which can be looped through to obtain the statuses. (Note this code block wasn't tested, so you may have to modify for your specific page)

One caveat is that array_groupBy() is not the most resource-efficient function, so it may use a lot of CPU or RAM if you are working with a large dataset, or have a high-traffic site. If it causes any performance issues let me know and I can look into other options.

Thanks!

Daniel
Technical Lead
interactivetools.com