Django Admin: Filtering Products Based on Parent and Child Collections

I'm using a self-referential relationship in my Django Collection model, and I want to modify the admin to filter products based on the collection. Currently, when the client filters products by a specific collection, it only shows products directly associated with that collection and not its child collections. I've modified the get_search_results function in the admin, but the generated query seems to append my filter with an AND condition instead of using OR.

`Here's the current implementation of the get_search_results function:

def get_search_results(self, request, queryset, search_term):

collection_filter = request.GET.get('collection__id__exact')
if collection_filter:
    try:
        collection_id = int(collection_filter)
        collection_q = Q(collection_id=collection_id) | Q(collection__parent_id=collection_id)
        queryset = queryset.filter(collection_q)
        print(queryset.query)
    except ValueError:
        pass
print(queryset.query)
return queryset, False
enter code her`

print result :

SELECT "shop_product"."id", "shop_product"."created_at", "shop_product"."updated_at", "shop_product"."deleted_at", "shop_product"."unit_price", "shop_product"."inventory", "shop_product"."min_inventory", "shop_product"."collection_id", "shop_product"."promotions_id", "shop_product"."discount_id", "shop_collection"."id", "shop_collection"."created_at", "shop_collection"."updated_at", "shop_collection"."deleted_at", "shop_collection"."parent_id" FROM "shop_product" INNER JOIN "shop_collection" ON ("shop_product"."collection_id" = "shop_collection"."id") LEFT OUTER JOIN "shop_product_translation" ON ("shop_product"."id" = "shop_product_translation"."master_id") WHERE ("shop_product"."collection_id" = 2 AND ("shop_product"."collection_id" = 2 OR "shop_collection"."parent_id" = 2)) ORDER BY "shop_product_translation"."title" ASC, "shop_product"."id" DESC

The resulting query appears to use AND conditions, which might be the reason for not including child collection products. How can I modify this to use OR conditions instead and ensure that products from both parent and child collections are included in the filtered results?

Any help in understanding and resolving this issue is appreciated.

This answer is without using any other library/dependency(using Django).

Assuming that you have a model structure similar to this:

class ShopCollection(models.Model):
    id = models.AutoField(primary_key=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    deleted_at = models.DateTimeField(null=True, blank=True)
    parent = models.ForeignKey("self", on_delete=models.CASCADE, null=True, blank=True)

    class Meta:
        verbose_name = "Shop Collection"
        verbose_name_plural = "Shop Collections"

    def __str__(self):
        return f"Collection ID: {self.id}"

class ShopProduct(models.Model):
    id = models.AutoField(primary_key=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    deleted_at = models.DateTimeField(null=True, blank=True)
    unit_price = models.DecimalField(max_digits=10, decimal_places=2)
    inventory = models.IntegerField()
    min_inventory = models.IntegerField()
    collection = models.ForeignKey(
        ShopCollection, on_delete=models.CASCADE, related_name="products"
    )
    promotions = models.ForeignKey(
        Promotion, on_delete=models.SET_NULL, null=True, blank=True
    )
    discount = models.ForeignKey(
        Discount, on_delete=models.SET_NULL, null=True, blank=True
    )

    class Meta:
        verbose_name = "Shop Product"
        verbose_name_plural = "Shop Products"

    def __str__(self):
        return f"Product ID: {self.id}"

And here are the changes to be made in the ShopProduct ModelAdmin in admin.py:

from django.contrib import admin
from django.db.models import Q
from .models import ShopProduct, ShopProductTranslation
from collection.models import ShopCollection


class CollectionFilter(admin.SimpleListFilter):
    title = "Collection"
    parameter_name = "collection"

    def lookups(self, request, model_admin):
        return ShopCollection.objects.values_list("id", "id")

    def queryset(self, request, queryset):
        if self.value():
            try:
                collection_id = self.value()
                queryset = queryset.filter(
                    Q(collection_id=collection_id)
                    | Q(collection__parent_id=collection_id)
                ).distinct()
            except ShopCollection.DoesNotExist:
                pass
        return queryset


class ShopProductAdmin(admin.ModelAdmin):
    list_display = (
        "id",
        "collection",
        "unit_price",
        "inventory",
        "min_inventory",
        "promotions",
        "discount",
        "created_at",
        "updated_at",
        "deleted_at",
    )
    list_filter = (
        CollectionFilter,  # Add the custom filter here
        "promotions",
        "discount",
        "created_at",
        "updated_at",
        "deleted_at",
    )
    search_fields = ("id",)
    ordering = ("id",)


admin.site.register(ShopProduct, ShopProductAdmin)

Results/Output:

Collection lists

Collection ID: 1 is parent for Collection ID: 2 and Collection ID: 4

Collection ID: 3 is parent for Collection ID: 5

Product lists

Product lists Filter by Collection ID: 1

Product lists Filter by Collection ID: 3

Product lists Filter by Collection ID: 2