[SOLVED] MySQL Combine Group by Column

Issue

I am new to SQL statements so my wording per my request may be incorrect, so I will provide a lot of detail to better understand my issue.

I have a database table called workouts that looks like this:

id bodyPart gifUrl name target broad_target
1 back http://d205bpvrqc9yn1.cloudfront.net/0007.gif alternate lateral pulldown lats back
2 chest http://d205bpvrqc9yn1.cloudfront.net/0009.gif assisted chest dip (kneeling) pectorals chest
3 lower legs http://d205bpvrqc9yn1.cloudfront.net/1708.gif assisted lying calves stretch calves legs
4 upper legs http://d205bpvrqc9yn1.cloudfront.net/1709.gif assisted lying glutes stretch glutes legs
5 upper legs http://d205bpvrqc9yn1.cloudfront.net/1710.gif assisted lying gluteus and piriformis stretch glutes legs
6 back http://d205bpvrqc9yn1.cloudfront.net/0015.gif assisted parallel close grip pull-up lats back

and I want it to combine all the broad_muscles together and wrap it under an array called data.

Ideally, it would look like this:

{
    title: 'Leg',
    data:[
        {
      "bodyPart": "lower legs",
      "equipment": "assisted",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/1708.gif",
      "id": "1708",
      "name": "assisted lying calves stretch",
      "target": "calves",
      "broad_target": "legs",
      "ppl": "legs"
    },
    {
      "bodyPart": "lower legs",
      "equipment": "smith machine",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/1396.gif",
      "id": "1396",
      "name": "smith toe raise",
      "target": "calves",
      "broad_target": "legs",
      "ppl": "legs"
    }
  ]
}

I will return it via. Lambda in Node.JS, so if needed, the answer can be how to parse it in Node.JS.

Thanks!

Solution

You can something do like this, but if your o/p formats in an array. You should use any SQL orms that can be helpful.

const sampleData = [{           
    "id":"1",
     "bodyPart":"back",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "alternate lateral pulldown" ,    
      "target": "lats",
      "broad_target" : "back"
},{
 "id":"2",
     "bodyPart":"chest",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "pectorals",
      "broad_target" : "chest"
},{
     "id":"2",
     "bodyPart":"upper legs",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "glutes",
      "broad_target" : "legs"
},{
     "id":"2",
     "bodyPart":"lower legs",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "calves",
      "broad_target" : "legs"
}];

const matchString = "legs";

const output = sampleData.reduce((prev, current) => {
    if (current?.broad_target === matchString) {
        prev['title'] = "Leg";
        prev['data'] = (prev['data'] || []);
        prev['data'].push(current);
    }
    return prev
},{});

console.log(output);

Note: I have taken sample o/p as the sample the information might not match the actual data.

Answered By – Apoorva Chikara

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.