python - 如何将 SQL 翻译成 SQLAlchemy？ (整形输出模型不同于数据库模型)

Question

我有以下 SQL 请求，它给了我想要的结果，但我无法成功地让它在 SQLAlchemy 中工作。

数据库模型

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import relationship

db = SQLAlchemy()

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    image = db.Column(db.String(255))
    brand = db.Column(db.String(255), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey("category.id"), nullable=False)
    prices = relationship("ArticlePrice", cascade="all, delete-orphan")

class ArticlePrice(db.Model):
    article_id = db.Column(db.Integer, db.ForeignKey("article.id"), primary_key=True)
    location_id = db.Column(db.Integer, db.ForeignKey("location.id"), primary_key=True)
    price = db.Column(db.Float, nullable=False)
    active = db.Column(db.Boolean, nullable=False)
    location = relationship('Location')

SQL 请求(工作)

我想返回位于给定位置并在此位置处于事件状态的所有 文章信息。加上它的价格，并且仅该位置的价格。

SELECT a.*, ap.price
FROM article a
INNER JOIN article_prices ap ON a.id = ap.article_id
WHERE ap.location_id = 1
AND ap.active = true

SQLAlchemy，使用 Flask(不工作)

@namespace.route('/location/<location_id>')
class ArticleLocatedAPI():
    @namespace.marshal_list_with(article, envelope='articles')
    def get(self, location_id):
        return Article.query.filter(Article.prices.any(active=True, location_id=location_id)).all()

输出模型

如您所见，我想要找到的文章的price，而不是返回价格数组的relationship。

article = namespace.model('Article', {
    'id': fields.Integer,
    'title': fields.String,
    'description': fields.String,
    'image': fields.String,
    'brand': fields.String,
    'price': fields.Float,
    'category_id': fields.Integer
})

问题

上面的代码为我提供了正确的文章。问题是它返回给我 relationship，这给了我一组价格。我想返回找到的文章的价格。我应该如何在 SQLAlchemy 中编写它？

这是正确的方法吗？

Answer 1

您可以使用显式 join() 将 Article.prices 集合限制为所有价格的子集。结合contains_eager()选项，如 "Using contains_eager() to load a custom-filtered collection result" 中所述:

return Article.query.\
    join(Article.prices).\
    filter_by(active=True, location_id=location_id).\
    options(db.contains_eager(Article.prices).load_only("price")).\
    all()

filter_by()从 Query 的主要实体中提取其关键字参数，或最后一个连接的目标实体，在本例中为 ArticlePrice。使用load_only()允许您限制从 ArticlePrice 加载的属性集。除了 ArticlePrice 的主键外，生成的 SQL 与您的原始 SQL 几乎完全相同 – you're using an ORM after all :

SELECT article_price.article_id AS article_price_article_id, article_price.location_id AS article_price_location_id, article_price.price AS article_price_price, article.id AS article_id, article.title AS article_title, article.description AS article_description, article.image AS article_image, article.brand AS article_brand, article.category_id AS article_category_id 
FROM article JOIN article_price ON article.id = article_price.article_id 
WHERE article_price.active = 1 AND article_price.location_id = ?

收件人rename并将价格集合解压缩为标量，将 attribute= 关键字参数传递给字段构造函数:

article = namespace.model('Article', {
    'id': fields.Integer,
    'title': fields.String,
    'description': fields.String,
    'image': fields.String,
    'brand': fields.String,
    'price': fields.Float(attribute=lambda a: a.prices[0].price),
    'category_id': fields.Integer
})