CODEWITHSUNDEEP
phpsqldataset
Malchesador
Malchesador

Reputation: 795

Filtering out multiple records in dataset

I'm working with a dataset coming back from a very complex view with multiple subselects and joins against several tables in a very large and convoluted database.

Each record has a structure like this:

MainValue = XXTS10, qtyPlaceholder1, qtyPlaceholder2, qtyPlaceholder3..., actualQty = 3, qtyPlaceholderKey = 1, color = blue.

MainValue = XXTS10, qtyPlaceholder1, qtyPlaceholder2, qtyPlaceholder3..., actualQty = 10, qtyPlaceholderKey = 3, color = blue.

MainValue = XXTS10, qtyPlaceholder1, qtyPlaceholder2, qtyPlaceholder3..., actualQty = 9, qtyPlaceholderKey = 2, color = blue.

So for each color and MainValue values, there are multiple records. I need to set the value of each qtyPlaceholder based on the actualQty where the qtyPlaceholderKey will tell me what value to put in each and derive only one record out of many so that the final single record looks like this:

MainValue = XXTS10, qtyPlaceholder1 = 3, qtyPlaceholder2 = 9, qtyPlaceholder3 = 10, color = blue.

I know I've done this hundreds of times over the years, but I'm just having mental block creating the proper looping structure and conditionals to create a single record out of many with the values mapped to the placeholders correctly. Trying to accomplish this in PHP, but it may be a good idea to reexamine the view and see if it can be adjusted, but I really don't want to go down that path if I can help it.

Any suggestions?

Upvotes: 1

Views: 62

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32402

Loop through your array and create a new array indexed by

  1. MainValue
  2. color
  3. qtyPlaceholderKey with a value of actualValue

Then 'flatten' the new array by looping through the new array and assigning key value pairs for MainValue, color and all qtyPlaceholderKeys and their corresponding actualValues.

$dbrows = array(
    array(
        'MainValue' => 'XXTS10',
        'actualQty' => 3,
        'qtyPlaceholderKey' => 1,
        'color' => 'blue',        
    ),
    array(
        'MainValue' => 'XXTS10',
        'actualQty' => 9,
        'qtyPlaceholderKey' => 2,
        'color' => 'blue',        
    ),
    array(
        'MainValue' => 'XXTS10',
        'actualQty' => 10,
        'qtyPlaceholderKey' => 3,
        'color' => 'blue',        
    ),    
);

$values = array();

foreach($dbrows as $r) {
    $values[$r['MainValue']][$r['color']][$r['qtyPlaceholderKey']] = $r['actualQty'];
}

$result = array();

foreach($values as $mainValue => $colorValues) {
    foreach($colorValues as $color => $qtyPlaceholderValues) {    
        $row = array('MainValue' => $mainValue, 'color' => $color);
        foreach($qtyPlaceholderValues as $qtyPlaceholderKey => $actualQty) {
            $row["qtyPlaceholderKey$qtyPlaceholderKey"] = $actualQty;
        }
        $result[] = $row;        
    }    
}

print_r($result);

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can do this in SQL using conditional aggregation. Here is a select form of the query:

select MainValue,
       max(case when qtyPlaceholderKey = 1 then actualQty end) as qtyPlaceholder1,
       max(case when qtyPlaceholderKey = 2 then actualQty end) as qtyPlaceholder2,
       max(case when qtyPlaceholderKey = 3 then actualQty end) as qtyPlaceholder3,
       color
from complexview v
group by MainValue, color;

Upvotes: 1

Related Questions