我找不到MYSQL查询来获得我想要的结果

Question

我有一个名为子类别的表，其列为'id‘和'name’，还有一个名为goals的表，其列为'id'，'name‘，外键为'subcategory_id’。

我想要一个结果为子类别对象数组的查询，它有一个属性'goals‘，这是一个目标对象数组。给出一个结果在JS代码中看起来如何的示例：

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

但是(使用不同的语法)对于其他语言，结果也是一样的。

到目前为止，我尝试使用left-join来做到这一点，如下所示：

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

但是这些目标并不是归为一个单独的子类别。我觉得查询应该是可行的，但是我不知道/google怎么做，因为我不知道如何表达这个问题，因为我缺乏SQL知识。

希望你们能帮助我！

提前谢谢。

Answer 1

最后，正如@tadman在他的评论中所建议的那样，我使用groupBy解决了这个问题。

我创建了一个函数(基于this answer中的信息)，如下所示：

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

这将导致具有分组目标的对象数组！因为我现在构造了这个函数(使用硬编码的键名)，它只适用于我的特定情况，如果你想泛化这个函数，你可以添加参数并用这些参数替换硬编码的键名。

Answer 2

你不能通过一个查询来获取它。MySQL不能这样做。

您当前正在获取所有目标，每个目标都有其子类别(子类别将重复)。

你可以用一些代码把它转换成想要的数组(例如在php中，你可以把它翻译成任何其他语言)。

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

但我认为，更有效的方法是使用多个查询：

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