PostgreSQL查询返回的值不在我的数据库中 [英] PostgreSQL query returning values that are not in my database

问题描述

我正在构建对数据库的查询以返回一些数据.这是前一篇文章的链接，该文章描述了我的意图

I am working on constructing a query to my database to return some data. Here is the link to a previous post describing my intentions Finding database data that best fits user variable responses. I want to return all of the columns for each data object, however the id that is returned is not correct and an additional VALUE field is being returned.

我的数据库是这样建立的场地

My database is set up like this venues

id  name  parking  decorations  hotel
1   park     1          2         1
2   beach    1          2         2
3   theater  2          2         2
4   yard     2          1         1

和一个枚举表

id     value
1      TRUE
2      FALSE
3      MAYBE

我正在后端上构建查询，如下所示:

I am building a query on my backend as follows:

  let searchConstraintsTrue = 'WHERE';
  let firstItemTrue = 0;
  for (const prop in req.body) {
    if (req.body[prop] === 'TRUE') {
      if (firstItemTrue === 0) {
        searchConstraintsTrue += ` ${prop} = 1`;
        firstItemTrue++;
      } else {
        searchConstraintsTrue += ` AND ${prop} = 1`;
      }
    }
  }

  let searchConstraintsMaybe = 'ORDER BY';
  let firstItemMaybe = 0;
  for (const prop in req.body) {
    if (req.body[prop] === 'MAYBE') {
      if (firstItemMaybe === 0) {
        searchConstraintsMaybe += ` (${prop} = 1)::integer`;
        firstItemMaybe++;
      } else {
        searchConstraintsMaybe += ` + (${prop} = 1)::integer`;
      }
    }
  }

  res.setHeader('Access-Control-Allow-Origin', 'http://localhost:3000');
  let sqlText = `SELECT * FROM venues 
  INNER JOIN response_enum rp ON rp.id = venues.parking 
  INNER JOIN response_enum rd ON rd.id = venues.decorations 
  INNER JOIN response_enum rh ON rh.id = venues.hotel 
  ${searchConstraintsTrue} ${searchConstraintsMaybe} DESC`;

我意识到我的 searchConstraintsTrue 和 searchConstraintsMaybe 不能正确使用枚举表，但现在我只是想让事情正常进行.

I realize that my searchConstraintsTrue and searchConstraintsMaybe are not properly using the enum table but right now I am just trying to get things working.

示例响应如下:

[  {
    id: 1,
    name: 'beach',
    parking: 1,
    decorations: 2,
    hotel: 1,
    value: 'TRUE'
  },
  {
    id: 2,
    name: 'yard',
    parking: 1,
    decorations: 2,
    hotel: 2,
    value: 'FALSE'
  }]

因此我返回了所需的数据，但是 id 的值不正确，并且数据库中不存在value列.

So I am returning the desired data however the id's are incorrect and there is a value column which doesn't exist in my database.

推荐答案

SELECT * 将选择联接表中的所有字段.您需要指定一个完全限定字段名称的列表，如下所示:

SELECT * will select all fields from the joined tables. You need to specify a list of fully qualified field names like so:

SELECT v.id,v.name,v.parking,v.decorations,v.hotel FROM venues v 
  INNER JOIN response_enum rp ON rp.id = venues.parking 
  INNER JOIN response_enum rd ON rd.id = venues.decorations 
  INNER JOIN response_enum rh ON rh.id = venues.hotel 
  ${searchConstraintsTrue} ${searchConstraintsMaybe} DESC

这篇关于PostgreSQL查询返回的值不在我的数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！