django tastypie : Getting extra-values of a m2m relationships using intermediate model

Question

I'm trying to use Tastypie with ManyToMany relationships using intermediate models (through keyword) (https://docs.djangoproject.com/en/dev/topics/db/models/#extra-fields-on-many-to-many-relationships)

I'm working with these models:

class Point(models.Model):
    ...
    value = models.FloatField(_('Value'), null=True)
    rooms = models.ManyToManyField('rooms.Room', through='points.PointPosition')

class Room(models.Model):
    title = models.CharField(max_length=64)

class PointPosition(models.Model):
    point = models.ForeignKey('points.Point', verbose_name=_('Point'))
    room = models.ForeignKey('rooms.Room', verbose_name=_('Room'))
    x = models.IntegerField(_('Y'))
    y = models.IntegerField(_('X'))

I've been able to fetch the many-to-many relationship, but not the extra fields. Here's my tastypie code:

class PointResource(ModelResource):
    class Meta:
        queryset = Point.objects.select_related(
            depth=10
            ).prefetch_related('rooms').all()
        resource_name = 'point'
        allowed_methods = ['get']

    ...
    value = fields.FloatField()
    rooms = fields.ToManyField('rooms.api.RoomResource', 'rooms', full=True)

class RoomResource(ModelResource):
    class Meta:
        queryset = Room.objects.all()
        resource_name = 'room'
        allowed_methods = ['get']

I've been trying to use a method to hydrate the room variable in my PointResource like this:

def dehydrate_rooms(self, bundle):                                                                                                                                                                                                                          
    rooms = []                                                                                                                                                                                                                                              
    for room in bundle.obj.rooms.all():                                                                                                                                                                                                                     
        position = PointPosition.objects.get(                                                                                                                                                                                                               
            room_id = room.pk,                                                                                                                                                                                                                              
            point_id = bundle.obj.pk)                                                                                                                                                                                                                                               
        rooms.append({'id': room.pk,                                                                                                                                                                                                                                 
             'title': room.title,                                                                                                                                                                                                                           
             'x': position.x,                                                                                                                                                                                                                               
             'y': position.y})                                                                                                                                                                                                                                               
    return rooms 

But the problem is that it creates as many query as I have points: it's a real performance killer when you have +8000 Points.

I haven't been able to find any useful resources to gain performance. I was thinking of doing a custom query using the .extra() method available for QuerySet, but the JOIN keyword isn't available (the patch has been refused a couple months ago). And I'm not sure SELECT subqueries would do the trick.

Answer 1

Have you considered changing your queryset to use the PointPosition resource? From the sounds of it what "Point" means in your database isn't actually the same as what "Point" means in your API so there needs to be some translation to hide the internal details:

class PointResource(ModelResource):
    class Meta:
        queryset = PointPosition.objects.select_related("point", "room")
        resource_name = 'point'
        allowed_methods = ('get', )

At the expense of needing to adjust your filtering parameters, this will avoid the need to do more than one query. Your dehydrate method can swap the data around as needed. You might also save some overhead by using .values() to only pull out the necessary fields as a dictionary rather than full objects.