翻译
Nested Queries with SQLAlchemy

​

在线开设热门课程的最有价值的方面之一是，我不时会收到一个迫使我学习新货色的问题。有一天，一位读者问我，他们如何编写一个不寻常排序的数据库查问，我不得不停下来一分钟（好吧，大略半个小时）思考如何在 Flask 和 Flask-SQLAlchemy 应用程序的上下文中执行这个查问。你筹备难看一些高级 SQLAlchemy 操作了吗？
​

问题

这个读者有一个带有订单表的数据库，看起来大略是这样的：

问题是如何依照 customer_id 字段对表中的我的项目进行排序，然而我的读者不是应用简略的升序或降序，而是须要依据客户最近一次订购的工夫来对列表进行排序。
​

所以基本上，现实的程序是这样的:

​

在这个表中，customer 2 的记录首先呈现，因为当你查看原始表时，你能够看到该客户在 2 月 7 日下了最近的订单，即 ID 为 9 的订单。这是存储在表中的最初一个订单，所以这个客户是最近的，因而它在客户中排在第一位。记录按订单日期降序排列。
​

下一组记录针对 customer 1，因为该客户是第二个最近的客户，其订单是 2 月 6 日。同样，该客户的三个订单彼此相邻，并按日期降序排列。
​

最初，customer 3 是三人中最早下订单的，最近的订单是在 2 月 1 日。该客户的四个订单排在底部，再次按订单日期降序排列。
​

我无奈立刻想出能够执行此操作的 SQLAlchemy 查问。在持续浏览之前，你是否想看看本人是否解决这个问题？为了让你更轻松，我在 GitHub 上创立了一个 gist，其中蕴含你能够应用的残缺工作示例应用程序和数据库。你须要做的就是获取文件并在正文批示的地位写下你的查问！
关上示例 gist​
​

要应用此应用程序，你须要创立一个虚拟环境并装置 flask-sqlalchemy。而后只需运行该脚本。在这个应用程序中，我应用的是内存中的 SQLite 数据库，因而你无需放心创立数据库，每次运行脚本时都会创立一个全新的，洁净的数据库。
​

如果你想晓得我是如何解决这个问题的，请持续浏览。
​

Subquery

上述问题的解决方案不能通过简略的查问来实现（至多我认为不能够，但很想被证实是谬误的！）。为了可能依据须要对行进行排序，咱们须要创立两个查问并将它们组合起来。
​

解决方案的第一局部是确定客户须要呈现在查问中的程序。为此，咱们基本上须要查看每个客户的最初一个订单。一个简略的办法是压缩或 group 订单表。
​

在关系数据库中，group 操作查看特定列的值，并将具备雷同值的所有行折叠到长期分组表中的单个行中。对于咱们的示例，当咱们按 customer_id 字段分组时，咱们最终会失去一个蕴含三行的分组表，每个客户一行。
​

然而，辣手的局部是如何将具备雷同 customer_id 的所有行合并为一行。为此，SQL 提供了聚合函数，这些函数承受值列表并生成单个后果。所有 SQL 实现中罕用的聚合函数有 sum、avg、min、max 等。在咱们的示例中，咱们想晓得每个客户的最初一个订单的日期，所以咱们能够应用 max(order_date) 来创立咱们的分组表。
​

我不确定这对其余 SQLAlchemy 开发人员是否实用，但对于不个别的查问，我发现应用原始 SQL 更容易找到解决方案，而后一旦有了它，我就将其改写为 SQLAlchemy。在 SQL 中，分组是通过以下语句实现的：

SELECT customer_id, max(order_date) AS last_order_date FROM orders GROUP BY customer_id

​

此查问将所有具备雷同 customer_id 的行合并为一个，对于 order_date 列，它将计算所有行的最大值并合并为一行。原始表还有 order 主键的 id 列，然而我没有在此查问的 SELECT 局部中援用此列，因而该列不会蕴含在分组后果中，这正是我想要的，因为我切实没有适合的办法来聚合 id 字段。
​

如果你对我提供的示例数据运行上述查问，后果将是：

​

当初咱们有了一个能够失常运行的 SQL 查问，咱们能够将它转换为 Flask-SQLAlchemy：

last_orders = db.session.query(Order.customer_id, db.func.max(Order.order_date).label('last_order_date')
).group_by(Order.customer_id).subquery()

​

如果你应用一般的 SQLAlchemy，你能够用你本人的会话对象替换下面的 db.session。Flask-SQLAlchemy 使解决会话变得更加容易，所以我总是更喜爱应用这个扩大。
​

如果你习惯于应用一般 SQLAlchemy 时运行以 Order.query.something 或 session.query(Order).something 结尾的查问，那么下面的内容看起来肯定很奇怪。请留神，在原始 SQL 示例中，我在 SELECT 局部有两个实体。你所看到的规范 SQLAlchemy 查问是一种简化模式，实用于从单个表中 查问整行 。如果查问返回来自两个或多个表的后果，或者在本例中，实在列和聚合列的组合，那么你必须应用这种更具体的模式，它须要 指定查问返回的列 作为 session.query() 办法的参数。
​

session.query() 的两个参数间接从 SQL 的 SELECT 转换而来，它们是 customer_id 列和 max(order_date) 聚合列。请留神 SQL 语句的 AS last_order_date 局部，它为聚合列提供了 别名。在 SQLAlchemy 中，利用于列的 label() 办法实现了雷同的后果。
​

分组是通过 group_by() 查询方法实现的，该办法应用列作为分组的参数，与 SQL 中的 GROUP BY 对应项雷同。
​

该语句以调用 subquery() 完结，它通知 SQLAlchemy 们的目标是在更大的查问中应用它，而不是独自应用它。实际上，这意味着咱们将可能将子查问对象视为真正的数据库表，而实际上它是动态创建的长期表。你将在下一节看到它是如何工作的。
​

Join

当初咱们晓得咱们心愿 customers 返回的程序，咱们必须将该订单合并到原始表中。最间接的办法是首先将 orders 表与上一节中取得的 last_orders 子查问合并。
​

为了合并关系数据库中的两个表，咱们应用 join 操作。join 将获取 orders 表中的每一行，将其与 last_orders 子查问中的相应行进行匹配，最初生成一个新组合行，该行蕴含两个表的列。join 操作的后果将是另一个动静表。应用原始 SQL，对子查问的连贯将按如下形式实现：

SELECT * FROM orders JOIN (SELECT customer_id, max(order_date) AS last_order_date FROM order GROUP BY customer_id
) AS last_orders
ON orders.customer_id = last_orders.customer_id

​

在这里，咱们在 JOIN(…) AS 名称结构中有上一节中的子查问，并将 last_orders 名称映射到子查问后果。而后查问的其余局部能够应用此名称来援用这些后果的各个列。ON 局部指定两个表的连贯条件，在这种状况下它是一个简略的条件，只匹配具备雷同 customer_id 值的行。
​

在咱们的示例中，join 将返回以下数据：

​

当初咱们有了每个客户退出订单的最初订单日期，咱们能够通过这个虚构的 last_order_date 列按降序对表进行排序，这满足咱们问题陈说中的第一个排序规范：

SELECT * FROM orders JOIN (SELECT customer_id, max(order_date) AS last_order_date FROM order GROUP BY customer_id
) AS last_orders
ON orders.customer_id = last_orders.customer_id
ORDER BY last_order_date DESC

​

然而咱们还没有实现，因为咱们须要实现一个二级排序。在每个客户中，咱们须要提供按订单日期降序排列的后果。这能够通过应用原始 order_date 字段的进行额定的排序来实现。
​

以下是残缺 SQL 语句：

SELECT * FROM orders JOIN (SELECT customer_id, max(order_date) AS last_order_date FROM order GROUP BY customer_id
) AS last_orders
ON orders.customer_id = last_orders.customer_id
ORDER BY last_order_date DESC, orders.order_date DESC

​

到 SQLAlchemy 的转换相当间接，但咱们将分离子查问以防止在单个语句中过于简单。这是上述查问的 SQLAlchemy 版本：

last_orders = db.session.query(Order.customer_id, db.func.max(Order.order_date).label('last_order_date')
).group_by(Order.customer_id).subquery()

query = Order.query.join(last_orders, Order.customer_id == last_orders.c.customer_id).order_by(last_orders.c.last_order_date.desc(), Order.order_date.desc())

last_orders 子查问是我在上一节中介绍的子查问的正本。请留神，此时尚未向数据库发送任何内容，提前将子查问存储在局部变量中不会触发额定的数据库查问。
​

在第二条语句中，咱们采纳 Order 模型并将其与 last_orders 子查问连接起来。子查问对象的工作形式与 SQLAlchemy 表相似，因而咱们能够应用 table.c.column_name 语法援用单个列。c 混同了很多人，可怜的是 SQLAlchemy 应用这个奇怪名称作为表对象中列的容器。
​

join() 办法承受两个参数，首先是连贯中的右侧表（last_orders 子查问），而后是连贯的条件，即两个表中的 customer_id 列匹配。连贯到位后，能够指定程序，这是 SQL 示例中两个程序语句的间接转换。请留神虚构 last_order_date 列是如何用那个奇怪的 c 作为 last_orders.c.last_order_date 援用的，然而 Order 模型中的 order_date 作为属性间接援用。这里的区别在于 Order 是一个模型，而 last_orders 是一个带有后果的通用表。模型具备比表更高级别的接口，因而它们更易于应用。
​

作为最初的练习，我想看看我的手工 SQL 与 SQLAlchemy 应用上述查问生成的 SQL 相比如何。如果你不晓得这个技巧，你能够通过将查问转换为字符串来获取 SQLAlchemy 为任何查问对象生成的 SQL：

print(str(query))

​

下面的 SQLAlchemy 查问生成以下原始 SQL：

SELECT 
    orders.id AS orders_id,
    orders.customer_id AS orders_customer_id,
    orders.order_date AS orders_order_date
FROM orders JOIN (
    SELECT
        orders.customer_id AS customer_id,
        max(orders.order_date) AS last_order_date
    FROM orders GROUP BY orders.customer_id
) AS anon_1
ON orders.customer_id = anon_1.customer_id
ORDER BY anon_1.last_order_date DESC, orders.order_date DESC

​

如果你疏忽这个生成的语句略微减少的简短，事件简直是一样的。SQLAlchemy 喜爱为查问中的每一列创立一个别名，因而你看到 AS 名称结构被大量应用。子查问与原始 SQL 雷同，但短少上下文的 SQLAlchemy 给了它一个通用的 anon_1 名称，而不是更明确的 last_orders。
​

译者注：
为了解决不同客户最初下单日期一样的场景，须要减少按 customer_id 进行排序
ORDER BY last_order_date DESC, orders.customer_id DESC, orders.order_date DESC

​

​