Show many to many relationship table in nodejs with mysql-CodePudding

I have the following tables, which have a relationship of many to many. Each project can have many technologies, and each technology can belong to many projects.

MariaDB [portfolioDB]> describe Projects_Project;
 ------------- -------------- ------ ----- --------- ---------------- 
| Field       | Type         | Null | Key | Default | Extra          |
 ------------- -------------- ------ ----- --------- ---------------- 
| projectId   | int(4)       | NO   | PRI | NULL    | auto_increment |
| name        | varchar(150) | NO   |     | NULL    |                |
| description | varchar(500) | NO   |     | NULL    |                |
| view        | varchar(200) | YES  |     | NULL    |                |
| code        | varchar(200) | YES  |     | NULL    |                |
| date        | date         | NO   |     | NULL    |                |
 ------------- -------------- ------ ----- --------- ---------------- 
6 rows in set (0.001 sec)

MariaDB [portfolioDB]> describe Projects_Technology;
 -------------- -------------- ------ ----- --------- ---------------- 
| Field        | Type         | Null | Key | Default | Extra          |
 -------------- -------------- ------ ----- --------- ---------------- 
| technologyID | int(4)       | NO   | PRI | NULL    | auto_increment |
| name         | varchar(150) | NO   |     | NULL    |                |
 -------------- -------------- ------ ----- --------- ---------------- 
2 rows in set (0.001 sec)

MariaDB [portfolioDB]> describe Projects_rel_Project_Technology;
 ------------ --------- ------ ----- --------- ------- 
| Field      | Type    | Null | Key | Default | Extra |
 ------------ --------- ------ ----- --------- ------- 
| Project    | int(11) | NO   | PRI | NULL    |       |
| Technology | int(11) | NO   | PRI | NULL    |       |
 ------------ --------- ------ ----- --------- ------- 
2 rows in set (0.001 sec)

This is how the relationship table was created, from

So the final question is that I don't know how to insert the query (the posted one) to get the technologies that belong to a project in the project data, because I can get them OR get the project data, can't get both.

mylist.push should be like this at the end I think

var arr_technologies = [];
Object.keys(results).forEach(function(key) {
    var row = results[key];
    arr_technologies.push({
        'name' : row.name
    });
}
mylist.push({ 
    'projectId' : row.projectId,
    'name' : row.name,
    'description' : row.description,
    'view' : row.view,
    'code' : row.code,
    'date' : row.date,
    'technologies' : arr_technologies
});

CodePudding user response：

Join all 3 tables. You can use GROUP_CONCAT() to get a comma-separated list of technology names for each project. Then you can use split() to split it into an array.

There's also no need to use Object.keys(results). results is an array, you can loop over it directly with map() or forEach().

app.get("/api/showProjects", function(request, response) {
  mysqlConnectionStart();

  var myquery = `
    SELECT p.*, GROUP_CONCAT(pt.name) AS technologies 
    FROM Projects_Project AS p
    JOIN Projects_rel_Project_Technology AS prpt ON prpt.Project = p.ProjectID
    JOIN Projects_Technology AS pt ON pt.technologyID = prpt.Technology
    GROUP BY p.ProjectID;`;

  connection.query(myquery, function(err, results, fields) {
      if (err) {
        console.log('----> Error with MySQL query in /showProjects: '   err.message);
      } else {
        console.log('Query successful, results for "'   myquery   '" are being displayed.');

        var mylist = results.map(row => ({
            'projectId': row.projectId,
            'name': row.name,
            'description': row.description,
            'view': row.view,
            'code': row.code,
            'date': row.date,
            'techology': row.technologies.split(',')
          })
        });

      response.send({
        "My projects": mylist
      });
    }
  });

mysqlConnectionEnd();
});