PostgreSQL查询返回的值不在我的数据库中

本文介绍了PostgreSQL查询返回的值不在我的数据库中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在构建对数据库的查询以返回一些数据.这是前一篇文章的链接，该文章描述了我的意图查找最适合用户变量响应的数据库数据.我想返回每个数据对象的所有列，但是返回的ID不正确，并且正在返回附加的 VALUE 字段.

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', '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