优化两个SQL查询和列表解析 [英] Optimise two SQL queries and a list comprehension

问题描述

我有这两个模型（简化）：

  class Place（OrderedModel）：
 name = models。 CharField（max_length = 100）
 
 class Team（models.Model）：
 name = models.CharField（max_length = 100）
 places = models.ManyToManyField（Place，blank =假设$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ C>。但只有 Team 的五个实例。我想以 Place 中的条目顺序返回布尔值列表，其中 True 表示 Team.places 包含该地方和 False （显然）意味着它没有。  Team.places 字段没有特定的顺序。
 
 
 
在以下示例中，团队具有前四个 Place 对象的实例，最后一个位置字段：
  [True，True，True，True，False，False，False，False，False，True] 
  
我在团队模型中使用此方法解决了这个问题：
  class Team（models.Model）：
 ... 
 def done（self）：

返回一个布尔值，每个地方一个
，这意味着团队已经找到
的地方，False，他们没有。

 places = Place.objects .all（）
返回[p in self.places.all（）for p in places] 
  
它的作品似乎效率很低。它使（我相信）两个不同的SQL查询，然后列表理解。有没有更有效的解决方法？
 
 
 
已解决
 
 
 
我最终这样做：
 
 
  def done（self）：

返回布尔值列表，每个地方一个
为顺序，表示团队已经找到
的地方，False，他们没有。

 sql ='''SELECT P.id，（TP。 id IS NOT NULL）已完成
从qrgame_place P 
 LEFT OUTER JOIN qrgame_team_places TP 
 ON P.id = TP.place_id AND TP.team_id =％s'''
 places = Place.objects.raw（sql，[self.id]）
 for p in places：
 yield bool（p.done）
  
 
 
解决方案
您需要此SQL查询：
 FROM myapp_place P 
 LEFT OUTER JOIN myapp_team_places TP 
 ON P.id = TP.place_id AND TP.team_id =％s 
  
（你还需要添加一个 ORDER BY 子句以您想要的顺序返回 Place 对象，但是由于我可以不能看到你的模型的那部分，我不知道这个条款应该是什么样子。）
 
 
 
我不知道如何使用Django的对象关系映射系统，但您可以始终使用原始SQL查询：
 >>> sql ='''...如上所述...'''
>>> places = Place.objects.raw（sql，[team.id]）
>>> for p in places：
 ... print p.id，bool（p.done）
 ... 
 1 True 
 2 True 
 3 False 
  
 
I have those two models (simplified):
class Place(OrderedModel):
    name = models.CharField(max_length=100)

class Team(models.Model):
    name = models.CharField(max_length=100)
    places = models.ManyToManyField(Place, blank=True)
Say that there are ten instances of Place. But only five instances of Team. I want to return a list of booleans in the order of entries in Place, where True means that Team.places contains that place and False (obviously) means that it doesn't. The Team.places field has no particular order.

In the following example the Team have the instances of the first four Place objects and the last one in it's places field:
[True, True, True, True, False, False, False, False, False, True]
I solved this with this method on the Team model:
class Team(models.Model):
    ...
    def done(self):
        """
        Returns a list of Booleans, one for each place
        in order. True means that the team has found the
        place. False, that they have not.
        """
        places = Place.objects.all()
        return [p in self.places.all() for p in places]
It works but seems highly inefficient. It make (I believe) two different SQL queries and then a list comprehension. Is there a more efficient way to solve this?

Solved

I ended up doing this:
def done(self):
    """
    Returns a list of Booleans, one for each place
    in order. True means that the team has found the
    place. False, that they have not.
    """
    sql = '''SELECT P.id, (TP.id IS NOT NULL) AS done
    FROM qrgame_place P
    LEFT OUTER JOIN qrgame_team_places TP
    ON P.id = TP.place_id AND TP.team_id = %s'''
    places = Place.objects.raw(sql, [self.id])
    for p in places:
        yield bool(p.done)

解决方案
You need this SQL query:
SELECT P.id, (TP.id IS NOT NULL) AS done
FROM myapp_place P
LEFT OUTER JOIN myapp_team_places TP
ON P.id = TP.place_id AND TP.team_id = %s
(You'll also need to add an ORDER BY clause to return the Place objects in the order you want, but since I can't see that part of your model, I can't tell what that clause ought to look like.)

I don't know how to express this query using Django's object-relational mapping system, but you can always run it using a raw SQL query:
>>> sql = ''' ... as above ... '''
>>> places = Place.objects.raw(sql, [team.id])
>>> for p in places:
...     print p.id, bool(p.done)
...
1 True
2 True
3 False


                        
这篇关于优化两个SQL查询和列表解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！