mysql - 在nodeJS中从MYSQL中的左连接查询中选择

Question

我正在尝试使用以下信息创建一个 json 对象

表格

1. 电影:主键MovieID

2. actors:主键ActorID

3. 出演:电影和 Actor 的 M 到 N 关系的中间表 主键 = MovieID 和 ActorID。

这就是我正在尝试做的事情。

1. 选择 ActorID=1 的 Actor

查询:

SELECT * FROM actors WHERE ActorID=1.

选择该 Actor 出演的所有电影。

查询:

SELECT * 
  from movies 
  LEFT 
  JOIN 
    ( SELECT *
       from actedin 
  WHERE ActorID = '+actorID+'
  ) AS actors_temp 
    ON movies.MovieID=actors_temp.MovieID AS movies_actor

求 RTRating 列的平均值。

查询:“从 movie_actor 中选择 AVG(RTRating)”；

求 MTRating 列的平均值。

查询:“从 movie_actor 中选择 AVG(MCRating)”；

这是我的nodeJS代码。

假设 app =express()，con = 连接到 mysql。我将查询结果附加到 actor 变量并通过 res.send 将它们发送回。

//#4 ActorID-> actor data, movies data, average rating 
app.get('/actor',function(req,res){
    let query_actorID = con.escape(req.query.actorID);
    let selectActor = 'SELECT * from actors WHERE ActorID = '+query_actorID;
        con.query(selectActor, function(err,result){
        if(err) throw err;
    console.log('retrieved actor info');
    console.log(result);
    var actor = result[0];
    var actorID = con.escape(actor.ActorID);
    //Retrieve Actor information
    let movie_query = 'SELECT * from movies LEFT JOIN (SELECT * from actedin WHERE ActorID = '+actorID+') AS actors_temp ON movies.MovieID=actors_temp.MovieID AS movies_actor'; 
    con.query(movie_query, function(err,movies){
    if(err) throw err;
        console.log('retrieved movies info');
        console.log(movies);
        actor.movies = movies;
        let RTR_query = 'SELECT AVG(RTRating) From movies_actor';
        con.query(RTR_query, function(err,average){
        if(err) throw err;
            console.log('retrieved movies info');
            console.log(average);
            actor.averageRTRating = average;
            });
        let MCR_query = 'SELECT AVG(MCRating) From movies_actor';
        con.query(RTR_query, function(err,average){
        if(err) throw err;
            console.log('retrieved movies info');
            console.log(average);
            actor.average_MCRating = average;
            });
        });

    res.send(JSON.stringify(actor));
    });
});

我的语法显然是错误的。我认为我错误地使用了 AS。

Answer 1

我还没有测试过，但它应该可以工作。Movie_query 已被简化。您必须考虑 RTR_query 和 MCR_query 返回的数据是一个数组。我已经做了一个查询，它会更有效。在您的代码中，您实际上使用了 RTR_query 代替了 MCR_query。我希望它会有所帮助。

//#4 ActorID-> actor data, movies data, average rating 
app.get('/actor',function(req,res){
    let query_actorID = con.escape(req.query.actorID);
    let selectActor = 'SELECT * from actors WHERE ActorID = '+query_actorID;
    con.query(selectActor, function(err,result){
        if(err) throw err;

        console.log('retrieved actor info');
        console.log(result);
        if (result.length == 1) {
            let actor = result[0];
            let actorID = con.escape(actor.ActorID);
            //Retrieve Actor information
            let movie_query = 'SELECT * from actedin INNER JOIN movies ON (movies.MovieID = actedin.MovieID) WHERE actedin.ActorID = ' + actorID; 
            con.query(movie_query, function(err,movies){
                if(err) throw err;

                console.log('retrieved movies info');
                console.log(movies);
                actor.movies = movies;

                let MCR_RTR_query = 'SELECT AVG(RTRating) AS rtrating_avg, AVG(MCRating) AS mcrating_avg From movies_actor';
                con.query(MCR_RTR_query, function(err,averages){
                    if(err) throw err;

                    console.log('retrieved movies info');
                    console.log(average);
                    actor.averageRTRating = averages[0].rtrating_avg;
                    actor.average_MCRating = averages[0].mcrating_avg;
                });
            });

            res.send(JSON.stringify(actor));
        }

    });
});