CODEWITHSUNDEEP
javascriptmysqlnode.js
Guinn
Guinn

Reputation: 1385

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

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.

Upvotes: 0

Views: 96

Answers (2)

Guinn
Guinn

Reputation: 1385

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.

Upvotes: 0

Gabriel
Gabriel

Reputation: 2190

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;
}

Upvotes: 1

Related Questions