CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Deadly
Deadly

Reputation: 2094

Select multiple objects many to many

There are following models:

class Schema(models.Model):
    keys = models.ManyToManyField(Key, through='SchemaHasKey')
    # ...

class Key(models.Model):
    name   = models.CharField(max_length=50)
    # ...

I need select 10 first schemas with the keys, but my way is bad:

schemas = []

for schema in Schema.objects.all().order_by('pk')[:10]:
    schema.key = schema.keys.all()
    schemas.append(schema)

.select_related() is not work well:

schemas = Schema.objects.select_related().order_by('pk')[:10]

This can be done in a certain number of sql queries?

Upvotes: 2

Views: 2378

Answers (2)

Yuji 'Tomita' Tomita
Yuji 'Tomita' Tomita

Reputation: 118458

Here's how I might do it until prefetch_related functionality is officially launched.

2 queries.

from collections import defaultdict

schemas = Schema.objects.order_by('pk')[:10]
key_map = defaultdict(lambda:[]) # always return a list

# use m2m through table to get all schema-key relationships related to the 10
# create a list of each `Key` with the Schema ID as the dict key.
[key_map[through.schema.id].append(through.key) for 
    through in Schema.keys.through.objects.filter(schema__in=schemas)]

for schema in schemas:
    schema.keys = key_map[schema.id]

Upvotes: 2

Lycha
Lycha

Reputation: 10177

Edit: prefetch_related only works in Django development version.

I think you can use djangos prefetch_related functionality. It also supports ManyToMany fields. It doesn't do the join in one SQL query but it might speed it up different ways.

It can be used like this:

Schema.objects.all().prefetch_related('keys').order_by('pk')[:10]

Upvotes: 1

Related Questions