Optimise two SQL queries and a list comprehension

Question

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)

Gareth Rees · Accepted Answer · 2013-04-09 11:35:56Z

1

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

answered Apr 9, 2013 at 11:35

Gareth Rees

66.1k10 gold badges139 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gareth Rees Over a year ago

Your loop at the end should be [bool(p.done) for p in places]

Niclas Nilsson Over a year ago

Oh, silly mistake to not catch. It was right in your code example. Work like a charm. I will, just for fun, time the different solution. But I would be surprised if this is not the mot optimized. Would it work on all major SQL-servers?

Gareth Rees Over a year ago

It's standard SQL so should run everywhere.

caio · Accepted Answer · 2013-04-09 14:29:00Z

1

A more optimal solution would be:

def done(self):    
    places = Place.objects.values_list('pk', flat=True)
    team_places = self.places.values_list('pk', flat=True)

    return [place in team_places for place in places]

answered Apr 9, 2013 at 14:29

caio

1,97916 silver badges20 bronze badges

2 Comments

Niclas Nilsson Over a year ago

Oh. Nice I don't have time just now. But will read up on on the documentation to understand why it's more optimized (I have a good guess what values_list does) and also time the different lookups.

Gareth Rees Over a year ago

I suggest team_places = set(...) so that the lookups are O(1).

Collectives™ on Stack Overflow

Optimise two SQL queries and a list comprehension

Solved

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Solved

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related