javascript - 如何删除重复结果并合并在 mySQL 查询中具有相似属性的对象？

Question

我将 mysql 库与 node.js 结合使用来创建此查询:

SELECT vols.id_vol, vols.id_place, vols.id_vol_type, vols.id_user_creator, vols.name, vols.desc, vols.date_creation, vols.date_begin, vols.date_end, vols.duration, ' +
            ' vols.active, vols.start_time, vols.end_time, vols.insurance, vols.deleted, users.id_user, users.login, users.verified, users.photo_url, comments.message, comments.id_user, place.id_place, place.name AS placeName, place.lat, place.long' +
            ' FROM vols INNER JOIN users ON vols.id_user_creator = users.id_user INNER JOIN place ON vols.id_place = place.id_place LEFT JOIN comments ON comments.id_vol = vols.id_vol WHERE vols.deleted = 0

返回这个:

  "success": true,
  "vols": [
    {
      "vol": {
        "id_vol": 1,
        "id_place": 1,
        "id_vol_type": 2,
        "id_user_creator": 1,
      },
      "users": {
        "id_user": 1,
        "login": "",
        "verified": 0,
      },
      "comments": {
        "message": "Muito fixe",
        "id_user": 3
      },
{
      "vol": {
        "id_vol": 1,
        "id_place": 1,
        "id_vol_type": 2,
        "id_user_creator": 1,
      },
      "users": {
        "id_user": 1,
        "login": "",
        "verified": 0,
      },
      "comments": {
        "message": "ola",
        "id_user": 3
      }

它目前正在为每个具有该特定卷 ID 的评论重复“卷”。是否可以停止显示重复项并将注释合并到一个数组中？

Answer 1

这不太可能，我的意思是您可以使用 group_concat 函数做一些事情，但这会很麻烦。

我在类似情况下通常做的是:

1. 查询出所有相关的vols
2. 提取所有你想看的userIDs, placeIDs, commentIDs。 (object) for each indexable parameter"user,place,comment")
3. 进行 3 个单独的查询，每个查询(用户、地点、评论)，您只选择相关元素(使用 WHERE id IN (1,5,7,8,10)过滤器)
4. 遍历每个查询的结果，并使用索引数组(对象)将它们插入到 vol 对象中

在 javascript 中可能是这样的(抱歉不知道 node.js，所以我没有确切的解决方案

var results = db.queryResults('SELECT vols.id_vol, vols.id_place, vols.id_vol_type, vols.id_user_creator, vols.name, vols.desc, vols.date_creation, vols.date_begin, vols.date_end, vols.duration, vols.active, vols.start_time, vols.end_time, vols.insurance, vols.deleted FROM vols WHERE vols.deleted = 0');
var lookupByUserID = {};
var lookupByPlaceID = {};
var lookupByVolID = {};
var userIN = [];
var placeIN = [];
var volIN = [];
for (var i = 0; i < results.length; i++)
{
   var result = results[i];
   if (!lookupByUserID[result.id_user_creator])
       lookupByUserID[result.id_user_creator] = [];
   lookupByUserID[result.id_user_creator].push(result);

   if(!lookupByPlaceID[result.id_place])
      lookupByPlaceID[result.id_place] = [];
   lookupByPlaceID[result.id_place].push(result);

   lookupByVolID[result.id_vol] = result;
   userIN.push(result.id_user_creator);
   placeIN.push(result.id_place);
   volIN.push(result.id_vol);
   results.comments = [];
}

var userResults = db.queryResults('SELECT users.id_user, users.login, users.verified, users.photo_url FROM users WHERE id_user IN ('+userIN.join(',')+')');
var placeResults = db.queryResults('SELECT place.id_place, place.name AS placeName, place.lat, place.long FROM place WHERE id_place IN ('+placeIN.join(',')+')');
var commentResults = db.queryResults('SELECT comments.id_vol, comments.message, comments.id_user FROM comments WHERE id_vol IN ('+volIN.join(',')+')');
for (var i = 0; i < userResults.length; i++)
{
   var user = userResults[i];
    for (var j = 0; j < lookupByUserID[user.id_user].length; j++)
        lookupByUserID[user.id_user][j].created_by_user = user;
}
for (var i = 0; i < placeResults.length; i++)
{
   var place = placeResults[i];
    for (var j = 0; j < lookupByPlaceID[place.id_place].length; j++)
        lookupByPlaceID[place.id_place][j].place = place;
}
for (var i = 0; i < commentResults.length; i++)
{
    var comment = commentResults[i];
    lookupByVolID[comment.id_vol].comments.push(comment);
}

现在对象“results”应该包含所有信息