django model object filter

Question

I have tables called 'has_location' and 'locations'. 'has_location' has user_has and location_id and its own id which is given by django itself.

'locations' have more columns.

Now I want to get all locations of some certain user. What I did is..(user.id is known):

users_locations_id = has_location.objects.filter(user_has__exact=user.id)
locations = Location.objects.filter(id__in=users_locations_id)
print len(locations)

but I am getting 0 by this print. I have data in db. but I have the feeling that __in does not accept the models id, does it ?

thanks

Answer 1

Using __in for this kind of query is a common anti-pattern in Django: it's tempting because of its simplicity, but it scales poorly in most databases. See slides 66ff in this presentation by Christophe Pettus.

You have a many-to-many relationship between users and locations, represented by the has_location table. You would normally describe this to Django using a ManyToManyField with a through table, something like this:

class Location(models.Model):
    # ...

class User(models.Model):
    locations = models.ManyToManyField(Location, through = 'LocationUser')
    # ...

class LocationUser(models.Model):
    location = models.ForeignKey(Location)
    user = models.ForeignKey(User)
    class Meta:
         db_table = 'has_location'

Then you can fetch the locations for a user like this:

user.locations.all()

You can query the locations in your filter operations:

User.objects.filter(locations__name = 'Barcelona')

And you can request that users' related locations be fetched efficiently using the prefetch_related() method on a query set.