Annotating Many2Many link works but only on SQLite (testing) not MariaDB (Production)

Question

I am trying to annotate a model that includes a many2many field:

class Link(models.Model):
    products = models.ManyToManyField(Product, related_name = "%(class)s_name", related_query_name = "product_link_qs", blank = True)
    position = models.ForeignKey(Position, on_delete = models.CASCADE)

class Position(models.Model):
    place = models.PositiveIntegerField(unique = True) 
    store = models.ForeignKey(Store, on_delete = models.CASCADE)

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

in my admin I used annotate() to couple the information:

@admin.register(Link)
class LinkAdmin(admin.ModelAdmin):
    list_display = ["product", "get_store"]
    list_filter = ["position__store"]

    ### extend by product_link_qs related name property to make field sortable in the admin
    def get_queryset(self, request):
        qs = super().get_queryset(request)
        return qs.annotate(storename = Product.objects.filter(product_link_qs = OuterRef("id")).values("store__name"))

    @admin.display(description = "store name", ordering = "storename")
    def get_store(self, obj):
        return obj.storename or None

only one product is linked to a position:

this works perfectly fine in SQLite on testing and MariaDB in production

adding a second product to a link:

works on SQLite in testing, but gives: django.db.utils.OperationalError: (1242, 'Subquery returns more than 1 row') on MariaDB

I worked around with putting a [:1] after my product filter: `qs.annotate(storename = Product.objects.filter(product_link_qs = OuterRef("id"))[:1].values("store__name"))` since the products of a Link can only come from the same store ... but I wonder how smart that approach is? — xtlc, Jun 24 '23 at 14:34

score 1 · Accepted Answer · answered Jun 24 '23 at 15:43

1

Likely the simplest way is to annotate it with the name of the store:

from django.db.models import F


@admin.register(Link)
class LinkAdmin(admin.ModelAdmin):
    list_display = ['product', 'get_store']
    list_filter = ['position__store']

    ### extend by product_link_qs related name property to make field sortable in the admin
    def get_queryset(self, *args, **kwargs):
        return (
            super()
            .get_queryset(*args, **kwargs)
            .annotate(storename=F('products__store__name'))
            .distinct()
        )

    @admin.display(description='store name', ordering='storename')
    def get_store(self, obj):
        return obj.storename or None

answered Jun 24 '23 at 15:43

Willem Van Onsem

443,496
30
428
555

Thanks for your input. But if I do that, I would have to distinguish between my testing and my productive environment, right? Because SQLite does not support `distinct` IIRC? Or would you suggest this is the way? – xtlc Jun 24 '23 at 20:08
1

@xtlc: SQLite definitely supports `.distinct()`. Only PostgreSQL supports `.distinct(..)` with field names, but we do not use that here. – Willem Van Onsem Jun 24 '23 at 20:09

Annotating Many2Many link works but only on SQLite (testing) not MariaDB (Production)

only one product is linked to a position:

adding a second product to a link:

1 Answers1