Examples of using OuterRef and Subquery

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.

No file or test is identified. Start by locating the documentation entry points for OuterRef, Subquery, and Prefetch, then compare them with the Product, Image, and Brand examples in the issue. Done means the relevant documentation includes clear examples for limited ManyToMany and ForeignKey prefetches, if maintainers confirm that is the intended change.