时间戳

首页 > 解决方案 > 如何在多对多关系Postgres中取回一行匹配

node.js - 如何在多对多关系Postgres中取回一行匹配

问题描述

id鉴于 users和 pets ，我正在尝试取回用户的宠物id。

我的表格如下

CREATE TABLE pet_owner (
  id serial PRIMARY KEY,
  first_name varchar(100) NOT NULL,
  last_name varchar(100) NOT NULL,
  phone_number varchar(50) UNIQUE,
  address varchar(255) UNIQUE
);

CREATE TABLE pet (
  id serial PRIMARY KEY,
  first_name varchar(100) NOT NULL,
  last_name varchar(100) NOT NULL,
  breed varchar(100) NOT NULL,
  weight numeric(5, 2),
  date_of_birth date,
  date_of_death date,
  address varchar(255) NOT NULL
);

CREATE TABLE pet_petowner (
  owner_id int REFERENCES pet_owner,
  pet_id int REFERENCES pet,
  PRIMARY KEY (owner_id, pet_id)
);

我的查询如下...

SELECT pet.id as id, pet.first_name as first_name, pet.last_name as last_name
FROM pet_petowner owner_pet
JOIN pet pet on (owner_pet.pet_id = 1)
WHERE owner_pet.owner_id = 1;

问题是即使具有指定的用户id不拥有该宠物，它似乎也得到了该宠物。如果是这种情况，我相信我不想返回任何东西。

我实际上是在我的快速应用程序中执行此查询，如下所示...

exports.getPet = async (req, res) => {
  const query = `SELECT pet.id as id, pet.first_name as first_name, pet.last_name as last_name
                 FROM pet_petowner owner_pet JOIN pet pet on (owner_pet.pet_id = ${req.params.pet_id})
                 WHERE owner_pet.owner_id = ${req.params.owner_id}`;
  const { rows } = await pool.query(query);
  res.status(200).send(rows[0]);
};

但显然，我有同样的问题。任何帮助，将不胜感激！

标签： node.jspostgresqlexpress

解决方案

查询需要指明如何连接两个表：

SELECT pet.id as id, pet.first_name as first_name, pet.last_name as last_name
FROM pet_petowner owner_pet
JOIN pet pet on (owner_pet.pet_id = pet.id)
WHERE owner_pet.owner_id = 1 AND pet.id = 1;

要在 node.js 中安全地使用查询，您必须转义参数或将它们作为占位符传递。https://www.atdatabases.org使用sql标签自动执行此操作，因此看起来像：

exports.getPet = async (req, res) => {
  const query = sql`
    SELECT pet.id as id, pet.first_name as first_name, pet.last_name as last_name
    FROM pet_petowner owner_pet
    JOIN pet pet on (owner_pet.pet_id = pet.id)
    WHERE owner_pet.owner_id = ${req.params.owner_id}
    AND pet.id = ${req.params.pet_id}
  `;
  const rows = await pool.query(query);
  res.status(200).send(rows[0]);
};

要做到这一点，您当前使用的库将执行以下操作：

exports.getPet = async (req, res) => {
  const query = `
    SELECT pet.id as id, pet.first_name as first_name, pet.last_name as last_name
    FROM pet_petowner owner_pet
    JOIN pet pet on (owner_pet.pet_id = pet.id)
    WHERE owner_pet.owner_id = $1
    AND pet.id = $2
  `;
  const rows = await pool.query(query, [
    req.params.owner_id,
    req.params.pet_id,
  ]);
  res.status(200).send(rows[0]);
};

推荐阅读