mysql - SQL输出参数失败

Question

所以我们有 2 个表:

列表

用户

目前，我正在尝试检索给定用户 ID 的所有信息，其中列表表的 fk_poster_id 是外键，并使用 GET 方法引用用户 ID。但是当我尝试执行代码时，我收到 [] 作为输出。有办法解决这个问题吗？

这是我当前的sql代码

DROP DATABASE snapsell;
CREATE DATABASE IF NOT EXISTS `snapsell`;
USE `snapsell`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `listings`;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  UNIQUE (username),
  profile_pic_url VARCHAR(1000) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
) ENGINE=INNODB;

-- THESE ARE JUST EXAMPLES AND TEST KITS.TO BE REMOVED BEFORE PRESENTATION.

INSERT INTO users (username, profile_pic_url) VALUES
("steve_jobs","https://upload.wikimedia.org/wikipedia/commons/thumb/f/f5/Steve_Jobs_Headshot_2010-CROP2.jpg/800px-Steve_Jobs_Headshot_2010-CROP2.jpg"),
("barack_obama","https://upload.wikimedia.org/wikipedia/commons/e/e9/Official_portrait_of_Barack_Obama.jpg"),
("kim_jung_un","https://upload.wikimedia.org/wikipedia/commons/d/d0/Kim_Jung-Un_-_Inter_Korean_Summit%28cropped%29_v1.jpg"),
("lee_kuan_yew","https://upload.wikimedia.org/wikipedia/commons/0/0f/Lee_Kuan_Yew.jpg");

CREATE TABLE listings (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  description_i VARCHAR(1000) NOT NULL,
  price INT(6) NOT NULL,
  fk_poster_id INT NOT NULL,
  KEY fkPosterID (fk_poster_id),
  CONSTRAINT FOREIGN KEY (fk_poster_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
) ENGINE=INNODB;

-- THESE ARE JUST EXAMPLES AND TEST KITS.TO BE REMOVED BEFORE PRESENTATION.
INSERT INTO listings (title, description_i, fk_poster_id, price) VALUES
("iPhone 6s USED","In good condition. Camera and screen not working.","2","250"),
("Samsung S7 NOT USED","In bad condition. Screen fully smashed. Can't even operate.","3","10000");

CREATE TABLE offers (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fk_offeror_id INT NOT NULL,
  KEY fkOfferID (fk_offeror_id),
  CONSTRAINT FOREIGN KEY (fk_offeror_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB;

SELECT * FROM users;
SELECT * FROM listings;

我当前的 Controller 代码

var express = require('express');

var app = express();
const userJs = require('../model/user')
const listingJs = require('../model/listing')
var bodyParser = require('body-parser');
var urlencodedParser = bodyParser.urlencoded({ extended: false });

app.use(bodyParser.json()); //parse appilcation/json data
app.use(urlencodedParser);
app.get("/users/:user_id/listings/",(req,res) => {
    var user_id = req.params.id;
    userJs.getListingsByUserID(user_id, (error,results) => {
        if (error) {
            res.status(500).send("Internal Server Error")
        }
        res.status(200).send(results);
    });
})

以及我当前的 user.js 代码

var db = require('./databaseConfig.js')
const users = {getListingsByUserID: function (user_id, callback) {
        const getListingsByUserIDQuery = 'SELECT u.id,l.title,l.description_i,l.price,l.fk_poster_id,l.created_at FROM listings l INNER JOIN users u ON u.id = l.fk_poster_id WHERE u.id = ?;';
        db.query(getListingsByUserIDQuery,[user_id],(error,results) => {
            if (error) {
                callback(error,null);
                return;
            };
            callback(null,results);
        })
    }

module.exports = users;

Answer 1

尝试使用 Promise 而不是像这样的回调:

    const users = 
    {
      function getListingsByUserID(user_id) 
    {
        return new Promise((resolve, reject) => 
        {
            db.query(getListingsByUserIDQuery,[user_id],(error,results) => 
            {
                if (error) 
                {
                    return reject(error);
                }
                else
                {
                    return resolve(results);
                }
            });
        });
    });
};

如果您想通过回调方法得到结果，请尝试callback(null,results[0]);