Merge row with the same product_id but different style_id in every row

Question

I have a problem achieving an output that I want. I have a table that contains product_id and style_id. Every product contains 1 or many different styles. The style_id column cannot be the same combination with any rows.

Note I want to achieve this result through SQL query or using PHP code.

product_style

id      product_id     style_id
1           1             1
2           1             2
3           1             3
4           1             4
5           2             1

And I want to achieve this result through SQL query or using PHP code.

  Array[12][
    {
      "id": 1,
      "name": "Product sample",
      "description": "Sample",
      "price": "11.00",
      "level": "school",
      "style_name":{[
           "Style 1",
           "Style 2",
           "Style 3",
           "Style 4"
          ]
      }
      "style_id": {[
          1,
          2,
          3,
          4,
        ]
      },
      "rank_id": 232
    }
    {
      "id": 2,
      "name": "Sample 2",
      "description": "Sample 2",
      "price": "10.00",
      "level": "school",
      "style_name": "Karate",
      "style_id": 1,
      "rank_id": 232
    }
  ]

this is my current code.

$grading_products= \DB::table('products as p')
                  ->leftjoin('product_style as ps', 'p.id', '=', 'ps.product_id')
                  ->join('style_users as su', 'ps.style_id', '=', 'su.style_id')
                  ->join('styles as s', 'ps.style_id', '=', 's.id')
                  ->whereRaw('su.user_id = ' .$id. ' AND p.product_type_id = 1 AND p.service_sub_type_id = 2')
                  ->select('p.id', 'p.name', 'p.description', 'p.price', 'p.level', 'ps.product_id', 's.name as style_name', 'ps.style_id as ps_style', 'su.style_id', 'su.rank_id')
                  ->get();

and this is my current output

Array[12][
  {
    "id": 1,
    "name": "Product Sample",
    "description": "Sample",
    "price": "11.00",
    "level": "school",
    "style_name": "style 1",
    "style_id": 1,
    "rank_id": 232
  },
  {
    "id": 1,
    "name": "Product Sample",
    "description": "Sample",
    "price": "11.00",
    "level": "school",
    "style_name": "style 2",
    "style_id": 2,
    "rank_id": 232
  }
  {
      "id": 2,
      "name": "Sample 2",
      "description": "Sample 2",
      "price": "10.00",
      "level": "school",
      "style_name": "Karate",
      "style_id": 1,
      "rank_id": 232
  },
]

Note I want to achieve this result through SQL query or using PHP code.

Database will give you a new row for each unique combination. You will have to iterate through the results in your script to create the array you are looking for. — Tim Morton, May 29 '18 at 12:27
yes, it will create a new row for every unique combination. That's why I'm asking for help in how could I achieve the desired output that I want. — Novice, May 29 '18 at 12:33
Have a look at GROUP_CONCAT : https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql — Frédéric Clausset, May 29 '18 at 12:42

score 0 · Answer 1 · answered May 29 '18 at 12:50

Using your current output as a starting place, here is a messy solution:

$new_output = [];

$keyed = [];

foreach ($current_output as $row) {
    $keyed[$row['id']]['style_name'][] = $row['style_name'];

    $keyed[$row['id']]['style_id'][] = $row['style_id'];

    $keyed[$row['id']]['row'] = $row;
}

foreach ($keyed as $id => $data) {
    $row = $data['row'];

    $temp_output = [
        'id' => $id,
        'name' => $row['name'],
        'description' => $row['description'],
        'price' => $row['price'],
        'level' => $row['level'],
    ];

    if (count($keyed[$row['id']]['style_name']) > 1) {
        $temp_output['style_name'] = $data['style_name'];
    } else {
        $temp_output['style_name'] = $data['style_name'][0];
    }

    if (count($keyed[$row['id']]['style_id']) > 1) {
        $temp_output['style_id'] = $data['style_id'];
    } else {
        $temp_output['style_id'] = $data['style_id'][0];
    }

    $temp_output['rank_id'] = $row['rank_id'];

    $new_output[] = $temp_output;
}

(not tested)

Merge row with the same product_id but different style_id in every row

1 Answers1