Examples of using OuterRef and Subquery

[sinaso]

These are a few examples related to conversations #336 and #236 Assume the following models:

class Product(models.Model):
    class Meta:
        db_table = "store_product"
        ordering = ['date_modified', ]

    id = fields.IntegerField(primary_key=True)
    name = fields.CharField(max_length=255)

    images = fields.ManyToManyField('store.Image', through='store.ProductImage',)
    brand = fields.ForeignKey('store.Brand', on_delete=fields.CASCADE, null=True,
        default=None, related_name='products')

class Image(models.Model):
    class Meta:
        db_table = "store_image"
        ordering = ['id', ]

    id = fields.IntegerField(primary_key=True)
    src = CharField(max_length=255)

class Brand(models.Model):
    class Meta:
        db_table = "store_brand"
        ordering = ['id', ]

    id = fields.IntegerField(primary_key=True)
    name = fields.CharField(max_length=255)

Note Product is in ManyToMany relationship with Image and ForeignKey (or OneToMany) relationship with Brand.

Also note that for the through field my code allows the model reference to be used, (tortoise only allows actual database table name). I have skipped the definition of ProductImage model here.

Now the following code prefetches only the first four images of a product. Notice the OuterRef and Prefetch on a ManyToMany field.

async def list_products_limit_images():
    subquery = Image.filter(products=OuterRef('products')).limit(4).values_list('id', flat=True)
    prefetch = Prefetch('images', queryset=Image.filter(id__in=Subquery(subquery)))

    async for product in Product.all().limit(5).prefetch_related(prefetch):
        print(f"{product.id} {product.name}")
        async for image in product.images:
            print(f"\timage: {image.id} {image.src}")

The following method, is almost the same thing. It prefetches the first six products for any brand. Notice the OuterReft and Prefetch on a ForeignKey field.

async def list_brands_prefetch_limited_products():
    subquery = Product.filter(brand=OuterRef('brand')).limit(6).values_list('id', flat=True)
    prefetch = Prefetch('products', queryset=Product.filter(id__in=Subquery(subquery)))

    async for brand in Brand.all().prefetch_related(prefetch):
        print(f"{brand.id} {brand.name}")
        for product in brand.products:
            print(f"\t{product.id} {product.name}")

hope this helps.

[grigi]

Thanks for the example. I have a better idea of the use case now.

What operands are supported for a subquery? I would assume __in and __not_in? Anything else? Another useful option would be __exists_in, as a useful optimization to generate slightly better SQL?

Also note that for the through field my code allows the model reference to be used, (tortoise only allows actual database table name). I have skipped the definition of ProductImage model here.

Probably more accurate to say that Tortoise doesn't support custom through tables at this stage for a ManyToManyField at all.

[sinaso]

Its pretty general, as it uses PyPika, I haven't played with it a lot, but I assume it could be anything.

Custom through tables were working fine actually, it's just that the default forward and backward table columns where being set to something really weird, (I cannot recall what it was exactly), once I provided forward and backward field names it was working fine.

In my code, I also fixed the default forward and backward field names to very obvious ones.

[grigi]

In my code, I also fixed the default forward and backward field names to very obvious ones.

Thanks for the heads up. We don't want to break existing users db's. That's way worse than breaking API.

[grigi]

Just a record: https://github.com/tortoise/tortoise-orm/compare/develop...nextfit:develop