具有多个联接的SQLAlchemy查询 [英] SQLAlchemy Query with Multiple Joins

问题描述

我正在使用SQLAlchemy和Postgres创建一个Flask应用程序。我对此非常环保，因此，我将提供任何反馈意见。但是，我的直接问题是在以下模型上构建查询。

I am creating a flask app with SQLAlchemy and Postgres. I am pretty green at this so I any feedback would be appreciated. However, my direct question is with constructing a query on the following model.

from app import db
from sqlalchemy import or_, and_


# Items Table
class Item(db.Model):

    __tablename__ = "items"

    id = db.Column(db.Integer, primary_key=True)
    itemName = db.Column(db.String, unique=True, nullable=False)
    measurement = db.Column(db.String, nullable=False)
    defaultPrice = db.Column(db.Float, nullable=False)
    minimumOrder = db.Column(db.Float, nullable=False)
    maximumOrder = db.Column(db.Float, nullable=False)
    orders = db.relationship('Order', back_populates='item')
    prices = db.relationship('Price', back_populates='item')

    def __init__(self, itemName, measurement, defaultPrice,
                 minimumOrder, maximumOrder):
        self.itemName = itemName
        self.measurement = measurement
        self.defaultPrice = defaultPrice
        self.minimumOrder = minimumOrder
        self.maximumOrder = maximumOrder

    def __repr__(self):
        return '<Item {0}>'.format(self.id)


# Users Table
class User(db.Model):

    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    fullName = db.Column(db.String, unique=True, nullable=False)
    userName = db.Column(db.String, unique=True, nullable=False)
    password = db.Column(db.String, nullable=False)
    role = db.Column(db.String, nullable=False)
    orders = db.relationship('Order', back_populates='user')
    prices = db.relationship('Price', back_populates='user')

    def __init__(self, fullName, userName, password, role):
        self.fullName = fullName
        self.userName = userName
        self.password = password
        self.role = role

    def __repr__(self):
        return '<User {0}>'.format(self.userName)


# Availability / Price Table
class Price(db.Model):

    __tablename__ = 'prices'

    id = db.Column(db.Integer, primary_key=True)
    userId = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship('User', back_populates='prices')
    itemId = db.Column(db.Integer, db.ForeignKey('items.id'))
    item = db.relationship('Item', back_populates='prices')
    available = db.Column(db.Boolean)
    priceMeasurement = db.Column(db.String)
    price = db.Column(db.Float)

    def __init__(self, userId, itemId, priceMeasurement, price):
        self.userId = userId
        self.itemId = itemId
        self.priceMeasurement = priceMeasurement
        self.price = price

    def __repr__(self):
        return '<Price {0}>'.format(self.price)


# Orders Table
class Order(db.Model):

    __tablename__ = 'orders'

    id = db.Column(db.Integer, primary_key=True)
    userId = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship('User', back_populates='orders')
    itemId = db.Column(db.Integer, db.ForeignKey('items.id'))
    item = db.relationship('Item', back_populates='orders')
    orderQuantity = db.Column(db.Float)
    orderMeasurement = db.Column(db.String)
    orderPrice = db.Column(db.Float)
    orderDelivery = db.Column(db.Date)
    orderPlaced = db.Column(db.Date)

    def __init__(self, userId, itemId, orderQuantity,
                 orderMeasurement, orderPrice, orderDelivery, orderPlaced):
        self.userId = userId
        self.itemId = itemId
        self.orderQuantity = orderQuantity
        self.orderMeasurement = orderMeasurement
        self.orderPrice = orderPrice
        self.orderDelivery = orderDelivery
        self.orderPlaced = orderPlaced

    def __repr__(self):
        return '<Order {0}>'.format(self.orderDelivery)

我想从查询中返回的表类似于以下查询返回的表：

What I would like from the query is to return a table similar to that which the following query returns:

SELECT * FROM items
JOIN prices ON prices.itemId=items.id
WHERE prices.userId = 1 AND prices.available = True
LEFT JOIN (
SELECT * FROM orders WHERE orderDelivery = '2017-07-05') as orders
ON orders.itemId=items.id

在SQLAlchemy查询中。我将把userId和orderDelivery变量从路由和会话传递给查询- @ app.route（’/ user / order /< order_date>’） | session ['userID'] ：在登录时建立。

In an SQLAlchemy query. I will pass the userId and orderDelivery variables to the query from the route and session - @app.route('/user/order/<order_date>') | session['userID'] : established at login.

谢谢

推荐答案

如果我对您的理解正确，那么您想查询（项目，价格，订单）实体的元组，其中Order来自子查询。在对象关系教程选择来自子查询的实体。

If I understood you correctly, you'd like to query tuples of (Item, Price, Order) entitites, where Order comes from the subquery. This is explained in the Object Relational Tutorial under Selecting Entities from Subqueries.

In [5]: from datetime import date

In [6]: orders_sq = db.session.query(Order).\
   ...:     filter(Order.orderDelivery == date(2017, 7, 5)).\
   ...:     subquery()

In [7]: orders_alias = db.aliased(Order, orders_sq)

In [8]: query = db.session.query(Item, Price, orders_alias).\
   ...:     join(Price).\
   ...:     outerjoin(orders_alias, Item.orders).\
   ...:     filter(Price.userId == 1,
   ...:            Price.available)

以及根据SQLite编译时生成的SQL：

and the produced SQL when compiled against SQLite:

In [9]: print(query)
SELECT items.id AS items_id, items."itemName" AS "items_itemName", items.measurement AS items_measurement, items."defaultPrice" AS "items_defaultPrice", items."minimumOrder" AS "items_minimumOrder", items."maximumOrder" AS "items_maximumOrder", prices.id AS prices_id, prices."userId" AS "prices_userId", prices."itemId" AS "prices_itemId", prices.available AS prices_available, prices."priceMeasurement" AS "prices_priceMeasurement", prices.price AS prices_price, anon_1.id AS anon_1_id, anon_1."userId" AS "anon_1_userId", anon_1."itemId" AS "anon_1_itemId", anon_1."orderQuantity" AS "anon_1_orderQuantity", anon_1."orderMeasurement" AS "anon_1_orderMeasurement", anon_1."orderPrice" AS "anon_1_orderPrice", anon_1."orderDelivery" AS "anon_1_orderDelivery", anon_1."orderPlaced" AS "anon_1_orderPlaced" 
FROM items JOIN prices ON items.id = prices."itemId" LEFT OUTER JOIN (SELECT orders.id AS id, orders."userId" AS "userId", orders."itemId" AS "itemId", orders."orderQuantity" AS "orderQuantity", orders."orderMeasurement" AS "orderMeasurement", orders."orderPrice" AS "orderPrice", orders."orderDelivery" AS "orderDelivery", orders."orderPlaced" AS "orderPlaced" 
FROM orders 
WHERE orders."orderDelivery" = ?) AS anon_1 ON items.id = anon_1."itemId" 
WHERE prices."userId" = ? AND prices.available = 1

此外，您也可以简单地将语句传递给 Query.from_statement 并进行了一些修复和更改：

Also alternatively you could simply pass your statement to Query.from_statement with a few fixes and changes:

In [45]: query2 = db.session.query(Item, Price, Order).\
    ...:     from_statement(db.text("""
    ...: SELECT * FROM items
    ...: JOIN prices ON prices.itemId=items.id
    ...: LEFT JOIN (
    ...: SELECT * FROM orders WHERE orderDelivery = :orderDelivery) as orders
    ...: ON orders.itemId=items.id
    ...: WHERE prices.userId = :userId AND prices.available
    ...: """)).\
    ...:     params(userId=1, orderDelivery='2017-07-05')

，但我建议使用前一种方法因为它与数据库无关。

but I'd recommend using the former approach as it is more database agnostic.

这篇关于具有多个联接的SQLAlchemy查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！