时间戳

首页 > 解决方案 > SQL INNER JOIN 返回外键值而不是主键值

sql - SQL INNER JOIN 返回外键值而不是主键值

问题描述

我有一个 Flask api，我正在从其中一个端点返回产品，并且我正在使用INNER JOIN查询来获取类别和用户信息。这是我的端点：

# Return all products
@products_api.route('/v1/resources/products/all', methods=['GET'])
def api_all():
    conn = databaseConnection()
    cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    sql_all_products = cur.execute('SELECT * FROM products a INNER JOIN users b ON a.user_id = b.id INNER JOIN product_categories c ON a.product_category_id = c.category_id ORDER BY a.id, b.id;')
    all_products = cur.fetchall()
    return jsonify(all_products)

响应是这样的：

[
      {
      "available_from": "Fri, 20 Sep 2019 11:00:00 GMT",
      "available_until": "Wed, 25 Sep 2019 12:00:00 GMT",
      "avatar_url": null,
      "category_id": 1,
      "category_name": "Power Tools",
      "created_date": "Mon, 16 Sep 2019 02:55:48 GMT",
      "description": "The desc.",
      "email": "benj@example.com",
      "email_confirmation_sent_time": "Mon, 16 Sep 2019 16:10:35 GMT",
      "email_confirmed": true,
      "email_confirmed_time": null,
      "firstname": "Ben",
      "id": 32,
      "is_draft": false,
      "last_updated_date": "Mon, 16 Sep 2019 02:55:48 GMT",
      "location": "Johannesburg",
      "parent_id": null,
      "product_category_id": 1,
      "quantity": 2,
      "quantity_available": 2,
      "rental_price": "40",
      "replacement_value": "600",
      "surname": "Jay",
      "title": "The title",
      "user_id": 32,
      "username": "test-user"
   },
      {
      "available_from": "Fri, 20 Sep 2019 00:00:00 GMT",
      "available_until": "Mon, 23 Sep 2019 00:00:00 GMT",
      "avatar_url": null,
      "category_id": 1,
      "category_name": "Power Tools",
      "created_date": "Mon, 16 Sep 2019 02:55:48 GMT",
      "description": "The test description.",
      "email": "benj@example.com",
      "email_confirmation_sent_time": "Mon, 16 Sep 2019 16:10:35 GMT",
      "email_confirmed": true,
      "email_confirmed_time": null,
      "firstname": "Ben",
      "id": 32,
      "is_draft": false,
      "last_updated_date": "Mon, 16 Sep 2019 02:55:48 GMT",
      "location": "Sandton",
      "parent_id": null,
      "product_category_id": 1,
      "quantity": 2,
      "quantity_available": 2,
      "rental_price": "60",
      "replacement_value": "400",
      "surname": "Jay",
      "title": "The test product",
      "user_id": 32,
      "username": "test-user"
   }
]

在我预期的响应中，id 将包含表的主键值，products但它返回表的主键值user(32)。如何更改此设置以id使响应中的值是产品 ID 而不是user_id值？

编辑 - 这里是列：

users

id, username, firstname, surname, email, password, email_confirmation_sent_time, email_confirmed, email_confirmed_time, avatar_url, created_date, last_updated_date

products

id, product_category_id, user_id, title, location, description, rental_price, quantity, quantity_available, replacement_value, available_from, available_until, is_draft, created_date, last_updated_date

product_categories

category_id, category_name, parent_id

标签： sqlinner-join

解决方案

您只需从第一个或主表中获取数据，对于其余表，仅获取选定的列，仅获取您想要的列（如有必要）。

试试这个

SELECT a.*, b.id as userID, b.username, b.firstname, b.surname, b.email, b.password, b.email_confirmation_sent_time, b.email_confirmed, b.email_confirmed_time, b.avatar_url, b.created_date, b.last_updated_date, c.category_id, c.category_name, c.parent_id FROM products a INNER JOIN users b ON a.user_id = b.id INNER JOIN product_categories c ON a.product_category_id = c.category_id ORDER BY a.id, b.id;

推荐阅读