annotation in admin list with many to many relation

Question

I have a link between ads and products and stores, and I want to sort them in the admin by store:

class Ad(models.Model):
    products = models.ManyToManyField(Product, blank = True)
    device = models.ForeignKey(Display, on_delete = models.CASCADE)

class Product(models.Model):
    name = models.CharField("name", max_length = 128)
    store = models.ForeignKey(Store, on_delete = models.CASCADE)

class Store(models.Model):
    name = models.CharField("name", max_length = 128)

so each Ad can have 0, 1, 2, 3 ... products linked to it. Now I want to make the field "store" sortable in the Ads admin list, therefore I tried to overwrite the get_queryset method of AdAdmin but got stuck on the way:

class AdAdmin(admin.ModelAdmin):
    list_display = ["get_store", ... ]

    def get_queryset(self, request):
        qs = super().get_queryset(request)
        qs = qs.annotate(storename = ####)
        return qs

    @admin.display(ordering = "storename")
    def get_store(self, obj):
        try:
            return obj.products.all().first().store.name
        except AttributeError:
            try:
                return obj.device.store.name
            except AttributeError:
                return "NEW"

So I want to annoatate my queryset by storename and I want to be able to sort by store.name alphabetically on my Ad list admin page. I already found out how to annotate empty stores:

qs = qs.annotate(storename = Case(When(products_pk__isnull = True, then = Value("NEW"), default = Value("old")))

But this only got me so far ... how would I assign the value store.name to default dynamically using my given logic?

Every Ad has a condition: It can only be linked to (many) products of the same store.

Answer 1

As you mentioned, every Ad can only have products from one store. Then you can take the store name from any Product or distinct result for all Products.

To access the Product from the Ad model, you can add a related_query_name

# models.py
class Ad(models.Model):
    products = models.ManyToManyField(
        Product,
        related_name="product_ads",
        related_query_name="product_ads_qs",
        blank=True,
    )
    ...

# admin.py
from django.db.models import OuterRef

@admin.register(Ad)
class AdAdmin(admin.ModelAdmin):
    list_display = ["get_store", ...]

    def get_queryset(self, request):
        qs = super().get_queryset(request)
        qs = qs.annotate(
            # Get all store names for each Ad object 
            storename=Product.objects.filter(product_ads_qs=OuterRef("id"))
            # Distinct the result. (Will fail it multiple store names returns)
            .distinct("store__name")
            # Get the store name
            .values("store__name")
        )
        return qs

    @admin.display(ordering="storename")
    def get_store(self, obj):
        # You can change the default value here. No need for Case operation
        return obj.storename or "NEW"

It should work. I am unsure about the performance, maybe a better ORM can be written.

UPDATE

Distinct can be replaced with [:1]. I forgot it yesterday, sorry. The store is FK for Product, but we need to be sure which Product instance we have from the query. That is why OuterRef is used. It means to get the Product instance for the given Ad instance by Ad.id.

There are several ways to access the store names. It is probably faster with the related name. I am not that experienced but will try to explain:

Without the related_name, this ORM can be written like storename=Ad.objects.filter(id=OuterRef("id")).values("products__store__name")[:1]

This ORM creates a query like the following. It started from the Ad table, and 3 joins the access to the store table.

SELECT
    "app_ad"."id", 
    (
        SELECT U3."name" 
        FROM "app_ad" U0 
        -- there is a reference table for m2m relation created by Django
        LEFT OUTER JOIN "app_ad_products" U1 ON (U0."id" = U1."ad_id") 
        LEFT OUTER JOIN "app_product" U2 ON (U1."product_id" = U2."id") 
        LEFT OUTER JOIN "app_store" U3 ON (U2."store_id" = U3."id") 
        -- OuterRef works here and searches the Ad.id per Ad record
        WHERE U0."id" = ("app_ad"."id") 
        -- [:1] limits the query
        LIMIT 1
    ) AS "storename" 
FROM "app_ad"

If we use a related name storename=Product.objects.filter(product_ads_qs=OuterRef("id")).values("store__name")[:1]

The query output has 2 joins to access the store table

SELECT "app_ad"."id",
    (
        SELECT U3."name" FROM "app_product" U0 
        INNER JOIN "app_ad_products" U1 ON (U0."id" = U1."product_id") 
        INNER JOIN "app_store" U3 ON (U0."store_id" = U3."id") 
        WHERE U1."ad_id" = ("app_ad"."id") LIMIT 1
    ) AS "storename"
FROM "app_ad"

Bonus: If you add the related name to Product model relation

class Product(models.Model):
    name = models.CharField("name", max_length=128)
    store = models.ForeignKey(
        Store,
        related_name="store_product",
        related_query_name="store_product",
        on_delete=models.CASCADE,
    )

The ORM can also be written as the following, and the query will be pretty much the same as the previous one. storename=Store.objects.filter(store_product__product_ads_qs__id=OuterRef("id")).values("name")[:1]

You can check for related naming convention.