CODEWITHSUNDEEP
djangodjango-queryset
Kevin Parker
Kevin Parker

Reputation: 17206

Django: Select matching foreign keys during filter

We have the following example models and are wondering if it's possible to select the foreign keys that match during the same query, possibly as an annotation. 

class BaseProduct(models.Model):
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)
    name = models.CharField(max_length=255)
    sub_title = models.CharField(max_length=255, blank=True, null=True)
    identifier_retailer = models.CharField(max_length=255)
    tags = models.CharField(max_length=255, blank=True, null=True)
    has_multiple_variants = models.BooleanField(default=False)

class BaseProductVariant(models.Model):
    product = models.ForeignKey(BaseProduct)
    name = models.CharField(max_length=128, blank=True, null=True)
    sub_title = models.CharField(max_length=255, blank=True, null=True)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)
    description = models.TextField(blank=True, null=True, help_text='Product description')
    features = models.TextField(blank=True, null=True, help_text='One feature per line')
    content = RedactorField(allow_image_upload=True, allow_file_upload=True, blank=True, null=True, help_text='Use this for rich HTML on featured products')
    warranty_string = models.CharField(max_length=255, blank=True, null=True)
    identifier_retailer = models.CharField(max_length=255, blank=True, null=True)
    identifier_upc = models.CharField(max_length=255, blank=True, null=True)
    identifier_model = models.CharField(max_length=255, blank=True, null=True)

We can query the results easily with BaseProduct.objects.filter()... but would like to select the list of matching BaseProductVariant's at the same time, otherwise we have to query the database in an unconventional fashion, and joining in python with prefetch_related on BaseProductVariant.objects.filter(product__in=[]).prefetch_related(product), select_related works on this too, but it a bit slower due to the extra deserialization on each row.

Upvotes: 0

Views: 328

Answers (1)

azundo
azundo

Reputation: 6052

You can use prefetch_related from BaseProduct to prefetch the variants with the related name. You can also use the Prefetch[1] object from django.db.models to control the attribute name where the prefetched variants end up:

from django.db.models import Prefetch

products_with_variants = BaseProduct.objects.all().prefetch_related(
    Prefetch('baseproductvariant_set', to_attr='variants'))

for p in products_with_variants:
    print(p.variants)

[1] https://docs.djangoproject.com/en/2.2/ref/models/querysets/#django.db.models.Prefetch

Upvotes: 1

Related Questions