头图

【翻译】使用 SQLAlchemy ORM 嵌套查询

PythonCN
翻译
Nested Queries with SQLAlchemy

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

问题

这个读者有一个带有订单表的数据库,看起来大概是这样的:

idcustomer_idorder_date
112018-01-01
212018-01-05
332018-01-07
412018-02-06
532018-01-31
622018-02-01
732018-02-01
832018-01-20
922018-02-07

问题是如何按照 customer_id 字段对表中的项目进行排序,但是我的读者不是使用简单的升序或降序,而是需要根据客户最近一次订购的时间来对列表进行排序。

所以基本上,理想的顺序是这样的:

idcustomer_idorder_date
922018-02-07
622018-02-01
412018-02-06
212018-01-05
112018-01-01
732018-02-01
532018-01-31
832018-01-20
332018-01-07

在这个表中, 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 实现中常用的聚合函数有 sumavgminmax 等。 在我们的示例中,我们想知道每个客户的最后一个订单的日期,所以我们可以使用 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 字段。

如果你对我提供的示例数据运行上述查询,结果将是:

customer_idlast_order_date
12018-02-06
22018-02-07
32018-02-01

现在我们有了一个可以正常运行的 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.somethingsession.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 将返回以下数据:

idcustomer_idorder_datelast_order_date
112018-01-012018-02-06
212018-01-052018-02-06
332018-01-072018-02-01
412018-02-062018-02-06
532018-01-312018-02-01
622018-02-012018-02-07
732018-02-012018-02-01
832018-01-202018-02-01
922018-02-072018-02-07

现在我们有了每个客户加入订单的最后订单日期,我们可以通过这个虚拟的 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

阅读 300

感谢订阅,我是大鹏。人生苦短,我用 Python。

3 声望
0 粉丝
0 条评论
你知道吗?

感谢订阅,我是大鹏。人生苦短,我用 Python。

3 声望
0 粉丝
宣传栏