Django查询大量的关系 [英] Django query for large number of relationships

问题描述

我使用以下方式设置Django模型：

模型A与模型B有一对多关系

A中的每个记录在B中都有3,000到15,000条记录

什么是构造一个查询的最佳方法， ）记录在B中对应于A中的每条记录的记录？这是我必须使用SQL代替Django ORM吗？

解决方案

创建一个帮助函数，顶部项。我在自己的Django应用中使用这个地方。

  def top_or_none（queryset）：
安全地拉出查询集中的顶部元素
＃提取单个元素集合w / top item 
 result = queryset [0：1] 
 
＃返回该元素或无如果没有任何匹配
返回结果[0]如果结果否
  

这使用了一个窍门w / 切片操作符添加您的SQL上的限制子句。

现在，在需要获取查询集的top项的任何地方使用此函数。在这种情况下，您想要获取给定A的前B个项目，其中B是按照pk降序排序的：

  latest = top_or_none（B.objects.filter（a = my_a）.order_by（' -  pk'））
  

$ b b

此外， Django Aggregation 这可以帮助你获得max pk，但我不喜欢这种情况下的解决方案，因为它增加了复杂性。

PS我不太喜欢依赖于'pk'字段这种类型的查询，因为一些RDBMS不保证顺序pks与逻辑创建顺序相同。如果我有一个表，我知道我将需要以这种方式查询，我通常有我自己的'创建'datetime列，我可以使用排序而不是pk。

基于评论进行修改：

如果您希望使用查询集[0]，则可以修改'top_or_none' ：

  def top_or_none（查询集）：
安全地取消查询集中的顶部元素 
 try：
 return Queryset [0] 
 except IndexError：
 return None 
  

我最初没有提议，因为我觉得queryset [0]会拉回整个结果集，然后取第0项。显然Django在这个场景中添加了一个LIMIT 1，所以它是我的切片版本的一个安全的替代品。

编辑2 >

当然，您还可以利用Django的相关管理器构造，并通过您的A对象构建查询集，具体取决于您的偏好：

  latest = top_or_none（my_a.b_set.order_by（' -  pk'））
  

I have Django models setup in the following manner:

model A has a one-to-many relationship to model B

each record in A has between 3,000 to 15,000 records in B

What is the best way to construct a query that will retrieve the newest (greatest pk) record in B that corresponds to a record in A for each record in A? Is this something that I must use SQL for in lieu of the Django ORM?

解决方案

Create a helper function for safely extracting the 'top' item from any queryset. I use this all over the place in my own Django apps.

def top_or_none(queryset):
    """Safely pulls off the top element in a queryset"""
    # Extracts a single element collection w/ top item
    result = queryset[0:1]

    # Return that element or None if there weren't any matches
    return result[0] if result else None

This uses a bit of a trick w/ the slice operator to add a limit clause onto your SQL.

Now use this function anywhere you need to get the 'top' item of a query set. In this case, you want to get the top B item for a given A where the B's are sorted by descending pk, as such:

latest = top_or_none(B.objects.filter(a=my_a).order_by('-pk'))

There's also the recently added 'Max' function in Django Aggregation which could help you get the max pk, but I don't like that solution in this case since it adds complexity.

P.S. I don't really like relying on the 'pk' field for this type of query as some RDBMSs don't guarantee that sequential pks is the same as logical creation order. If I have a table that I know I will need to query in this fashion, I usually have my own 'creation' datetime column that I can use to order by instead of pk.

Edit based on comment:

If you'd rather use queryset[0], you can modify the 'top_or_none' function thusly:

def top_or_none(queryset):
    """Safely pulls off the top element in a queryset"""
    try:
        return queryset[0]
    except IndexError:
        return None

I didn't propose this initially because I was under the impression that queryset[0] would pull back the entire result set, then take the 0th item. Apparently Django adds a 'LIMIT 1' in this scenario too, so it's a safe alternative to my slicing version.

Edit 2

Of course you can also take advantage of Django's related manager construct here and build the queryset through your 'A' object, depending on your preference:

latest = top_or_none(my_a.b_set.order_by('-pk'))

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