Can't figure out MYSQL query to get the result I want

Question

I have a table called subcategories with columns 'id' and 'name' and a table called goals with columns 'id', 'name' and foreign key 'subcategory_id'.

I want a query that results in an array of subcategory objects, which has a property 'goals' which is an array of goal objects. Too give an example of how the result would look In JS code:

result = [
           {id: 1, name: "name", goals: [{id: 1, name: "goalName"}, {...},  {...}]}, 
           {...}, 
           {...}
         ]

But (with a different syntax) the result would be the same for other languages..

Thusfar I tried to do this with left-join, like this:

SELECT sc.ID as subcatId, sc.name as subcatName, g.ID as ID, g.name as name 
FROM needs_subcategories as sc 
LEFT JOIN needs_goals as g 
ON sc.ID=g.subcategory_id

But the goals aren't grouped under a single subcategory.. I feel like it should be possible to do with a query, but I can't figure out/google how to do it because I wouldn't know how to phrase the question due to my lack of SQL knowledge..

Hope you guys can help me!

Thanks in advance.

Last time I did check there was no direct access to mysql from JS, so.. — , Jan 23 '17 at 19:56
There is no way that single query gives you above result. Get all data and transform data as you need. lodashjs may be very helpful tool. if you are getting data in nodejs app or sending data to browser. — gaurang171, Jan 23 '17 at 19:56
@RC. i just used JS format to illustrate the array i want returned.. I'm using NodeJS which uses the same format — Guinn, Jan 23 '17 at 19:57
I see, don't you think you should tell that in your question? So you should edit and post your nodeJs code (and add a nodeJs tag) — , Jan 23 '17 at 20:00
@RS. I don't see how that helps in answering the question tbh, the question is about a query which would return an array structured like that, regardless of the language.. — Guinn, Jan 23 '17 at 20:01
Why? It is quite easy to group results in a couple of lines. You can use `group_concat` with `group by sc.ID` to get a list, but you will need to convert it nonetheless. — shukshin.ivan, Jan 23 '17 at 20:09
@Guinn It's important to tag your questions correctly so the right people see them. This is a Node question, so I've tagged it that way. — tadman, Jan 23 '17 at 20:14
This sounds like a job for [`groupBy`](https://lodash.com/docs/#groupBy). — tadman, Jan 23 '17 at 20:16
@tadman thanks for adding the tag then, i'll be more specific next time. groupBy did the trick! Thanks for that tip! — Guinn, Jan 23 '17 at 21:28
@Guinn Good to hear. If you've got a solution that works, it's worth adding it as an answer to help explain better plus as an example for others battling the same problem. — tadman, Jan 23 '17 at 21:30

Gabriel · Answer 1 · 2017-01-23T21:06:02.377

You won't be able to acheive that with a query. MySQL can't do that.

You are currently fetching all goals, each one with their subcategory (subcategories will repeat).

You can convert it to the desired array with some code (example in php, you can translate this to any other language).

$result=array();
$lastSubcatId=null;
$goals=array();
while($row=$query->fetch_object()) { //assuming $query is the resultset
    if($lastSubcatId&&$lastSubcatId!=$row->subcatId) {
        $row->goals=$goals;
        $result[]=$row; //or you could assign each desired property
        $goals=array();
    }
    $goals[]=$row; //or you could assign each desired property
}
//surely, there are items left in $goals
if($lastSubcatId) {
    $row->goals=$goals;
    $result[]=$row; //or you could assign each desired property
}

But a more efficient way would be, I think, with multiple queries:

$result=array();
$subcats=$db->query("SELECT * FROM needs_subcategories");
while($subcat=$subcats->fetch_object()) {
    //you might want to use prepared statements, I'm just simplifying
    //it will not only be safer, but reusing the prepared statement will increase the performance considerably
    $goals=$db->query("select * from needs_goals where subcategory_id=".$subcat->ID); 
    $temp=array();
    while($goal=$goals->fetch_object()) $temp[]=$goal;
    $subcat->goals=$temp;
    $result[]=$subcat;
}

I hope you don't do this in production code because slamming stuff into your query using string concatenation is scary. Use prepared statements with placeholder values. These are also possible in Node.js with [Sequelize](http://sequelizejs.com). — tadman, Jan 23 '17 at 20:15
Yes... That's what the comment says. "you might want to use prepared statements, I'm just simplifying". — Gabriel, Jan 23 '17 at 20:16
It's ok, thank you for pointing it out, others may not see it either. — Gabriel, Jan 23 '17 at 20:25

score 0 · Accepted Answer · edited May 23 '17 at 11:52

In the end I solved this using groupBy as @tadman suggested in his comment.

I created a function (based on the information in this answer) that looks like this:

function processResults(collection, groupKey) {
    var result = _.chain(collection)
                  .groupBy(groupKey)
                  .toPairs()
                  .map(function (currentItem) {
                      // 'text' and 'children' are the keys I want in my resulting object
                      // children being the property that contains the array of goal objects
                      return _.zipObject(['text', 'children'], currentItem);
                  })
                  .value();
    return result;
}

Which results in the array of objects with grouped goals! As I structured the function now (with hard-coded key names) it only works for my specific case, if you want to generalize the function you could add parameters amd replace the hard-coded key names with those.

Can't figure out MYSQL query to get the result I want

2 Answers2