TranslationNestedQuerieswithSQLAlchemy在线热门课程最有价值的一个方面是，我时不时地得到一个问题，迫使我学习新的东西。有一天，一位读者问我他们如何编写一个具有不寻常顺序的数据库查询，我不得不停下来一分钟（好吧，也许是半小时）来考虑如何在Flask和Flask-SQLAlchemy的上下文中执行它应用此查询。您准备好了解一些高级SQLAlchemy的实际应用了吗？问题这位读者有一个带有订单表的数据库，如下所示：idcustomer_idorder_date112018-01-01212018-01-05332018-01-07412018-02-06532018-01-31622018-02-01732018-02-01832018-01-20922018-02-07问题是如何根据customer_id字段对表中的项目进行排序，但我的读者需要根据客户最后一次订购的时间对列表进行排序，而不是使用简单的升序或降序。所以基本上，理想的顺序是这样的：idcustomer_idorder_date922018-02-07622018-02-01412018-02-06212018-01-05112018-01-01732018-02-01532018-01-31832018-01-2031-038-038-01在此表中，客户2的记录首先出现，因为当您查看原始表时，您可以看到该客户在2月7日下了最近的订单，订单ID9。这是表中存储的最后一个订单，所以这个客户是最近的，所以在客户中排在第一位。记录按订单日期降序排列。下一组记录是针对客户1的，因为该客户是第二个最近的客户，其订单是在2月6日下的。同样，客户的三个订单按日期降序排列。最后，客户3是三人中最早下单的，最近一次下单时间是2月1日。客户的四个订单在底部，再次按订单日期降序排列。我无法立即想到可以执行此操作的SQLAlchemy查询。在继续阅读之前，您想看看自己是否可以解决这个问题吗？为了方便您，我在GitHub上创建了一个要点，其中包含您可以使用的完整的工作示例应用程序和数据库。您需要做的就是获取文件并在注释指示的地方写下您的查询！打开示例要点要使用此应用程序，您需要创建一个虚拟环境并安装flask-sqlalchemy。然后只需运行脚本。在此应用程序中，我使用内存中的SQLite数据库，因此您无需担心创建数据库的问题，它会在您每次运行脚本时创建一个全新、干净的数据库。如果你想知道我是如何解决这个问题的，请继续阅读。子查询上述问题的解决方案不能用简单的查询来完成（至少我认为不能，但很想被证明是错误的！）。为了能够根据需要对行进行排序，我们需要创建两个查询并将它们组合起来。解决方案的第一部分是确定客户需要在查询中出现的顺序。为此，我们基本上需要查看每个客户的最后订单。一种简单的方法是对订单表进行压缩或分组。在关系数据库中，组操作查看特定列的值，并将具有相同值的所有行折叠到临时分组表中的单个行中。对于我们的示例，当我们按customer_id字段分组时，我们最终得到一个包含三行的分组表，每个客户一行。然而，棘手的部分是如何将具有相同customer_id的所有行合并为一行。为此，SQL提供了聚合函数，它接受值列表并产生单个结果。所有SQL实现中常用的聚合函数有sum、avg、min、max等。在我们的示例中，我们想知道每个客户的最后一个订单的日期，因此我们可以使用max(order_date)来创建我们的分组表。我不确定这是否适用于其他SQLAlchemy开发人员，但对于不寻常的查询，我发现使用原始SQL更容易找到解决方案，然后一旦有了它，我就会将其重写到SQLAlchemy中。在SQL中，分组是通过以下语句完成的：SELECTcustomer_id,max(order_date)ASlast_order_dateFROMordersGROUPBYcustomer_id该查询将具有相同customer_id的所有行合并为一个，对于order_date列，它统计最大值和合并为一行。原始表也有一个用于订单主键的id列，但我没有在该查询的SELECT部分引用该列，因此该列不会包含在分组结果中，这正是我想要的，因为我真的没有合适的方法来聚合id字段。如果您对我提供的示例数据运行上述查询，结果将是：customer_idlast_order_date12018-02-0622018-02-0732018-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.something或session.query(Order).something开头的查询，那么上面的内容一定看起来很奇怪。请注意，在原始SQL示例中，我在SELECT部分中有两个实体。您看到的标准SQLAlchemy查询是一种简化形式，适用于从单个表中查询整行。如果查询从两个或多个表返回结果，或者在这种情况下是真实列和聚合列的组合，那么您必须使用这种更详细的形式，这需要将查询返回的列指定为session.query()方法参数.session.query()的两个参数是直接从SQLSELECT转换过来的，分别是customer_id列和max(order_date)聚合列。请注意SQL语句的ASlast_order_date部分，它为聚合列提供别名。在SQLAlchemy中，应用于列的label()方法可获得相同的结果。分组是通过group_by()查询方法完成的，该方法使用列作为分组的参数，与SQL中的GROUPBY对应项相同。该语句以对subquery()的调用结束，它告诉SQLAlchemy它打算在更大的查询中使用，而不是单独使用。实际上，这意味着我们可以将子查询对象视为真实的数据库表，而实际上它是动态创建的临时表。您将在下一节中看到这是如何工作的。Join现在我们知道我们希望客户退回的订单，我们必须将该订单合并到原始表中。最直接的方法是先将orders表和上一节得到的last_orders子查询合并。要合并关系数据库中的两个表，我们使用连接操作。连接将获取orders表中的每一行，将其与last_orders子查询中的相应行匹配，最后生成一个包含两个表中的列的新组合行。连接操作的结果将是另一个动态表。使用原始SQL，子查询上的连接将按如下方式完成：SELECT*FROMordersJOIN(SELECTcustomer_id,max(order_date)ASlast_order_dateFROMorderGROUPBYcustomer_id)ASlast_ordersONorders.customer_id=last_orders.customer_id我们在这里JOIN(...)ASname构造您拥有上一节中的子查询并将last_orders名称映射到子查询结果。然后查询的其他部分可以使用此名称来引用这些结果的各个列。ON部分指定了两个表的连接条件，在本例中是一个简单的条件，只匹配具有相同customer_id值的行。在我们的示例中，连接将返回以下数据：idcustomer_idorder_datelast_order_date112018-01-012018-02-06212018-01-052018-02-06332018-01-072018-02-01412018-02-062018-02182-018-023203-03203-03203-01622018-02-012018-02-07732018-02-012018-02-01832018-01-202018-02-01922018-02-072018-02-07现在我们有每个加入订单的客户的最后订单日期，我们可以按这个虚拟last_order_date列对表进行降序排序，它满足我们问题陈述中的第一个排序标准：SELECT*FROMordersJOIN(SELECTcustomer_id,max(order_date)ASlast_order_dateFROMorderGROUPBYcustomer_id)ASlast_ordersONorders。customer_id=last_orders.customer_idORDERBYlast_order_dateDESC但是我们还没有完成，因为我们需要实现二级订单。在每个客户中，我们需要提供按订单日期降序排列的结果。这可以通过使用原始order_date字段的额外排序来完成。以下是完整的SQL语句：SELECT*FROMordersJOIN(SELECTcustomer_id,max(order_date)ASlast_order_dateFROMorderGROUPBYcustomer_id)ASlast_ordersONorders.customer_id=last_orders.customer_idORDERBYlast_order_dateDESC,orders.order_datechemyDESC转换为算法DESC非常简单，但我们将分离子查询以避免在单个语句中过于复杂。这是上述查询的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()我在上一节中描述的子查询的副本。请注意，此时尚未向数据库发送任何内容，提前将子查询存储在局部变量中不会触发额外的数据库查询。在第二条语句中，我们采用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：SELECTorders.idASorders_id,orders.customer_idASorders_customer_id,orders.order_dateASorders_order_dateFROM订单JOIN(SELECTorders.customer_idAScustomer_id,max(orders.order_date)ASlast_order_dateFROMordersGROUPBYorders.customer_id)ASanon_1ONorders.customer_id=anon_1.customer_idORDERBY匿名_1。last_order_dateDESC,orders.order_dateDESC如果你忽略这个生成的语句稍微增加的冗长，事情几乎是一样的。SQLAlchemy喜欢为查询中的每一列创建一个别名，因此您会看到AS名称构造被大量使用。子查询与原始SQL相同，但SQLAlchemy缺少上下文，因此使用了通用的anon_1名称，而不是更明确的last_orders。译者注：为了解决不同客户最后下单日期相同的场景，需要添加按customer_id排序ORDERBYlast_order_dateDESC,orders.customer_idDESC,orders.order_dateDESC
