Query Model with Many to Many Field with list as input

Using Django Using the ORM
1

I have a model that contains a many to many field:

class Transaction(models.Model): 
    id = models.UUIDField(default=uuid.uuid4, unique=True, editable=False, primary_key=True)
    transaction_date = models.DateField(null=False, blank=False)
    transaction_category = models.ForeignKey(Category, on_delete=models.PROTECT, null=False, blank=False)
    customer_vendor = models.ForeignKey(CustomerVendor, on_delete=models.PROTECT, blank=False, null=False)
    account = models.ForeignKey(Account, on_delete=models.PROTECT, blank=False, null=False)
    reimbursable = models.BooleanField()
    tag = models.ManyToManyField(Tag, blank=True)
    amount = models.DecimalField(max_digits=12, decimal_places=2, null=False, blank=False)
    description = models.CharField(max_length=255, null=False, blank=False)

I have a query that gets me transactions:

Transaction.objects.filter(transaction_category__master_category__category_type="Income", transaction_date__range=[start_date, end_date]).filter(exclude_reimbursable).order_by().annotate(month=ExtractMonth("transaction_date"), year=ExtractYear("transaction_date")).values("month", "year").annotate(month_year=Concat("month", V(" "), "year", output_field=CharField()), month_total=Sum("amount")).values("month_year", "month_total").order_by("year", "month")

I am trying to add the Tag (many to many field) to the query. From the web app, I get all the tags that a user selected as a list. The list could also be empty and have 0 tags.

I tried using tag__in=tags in the query, but if a user does not select any tags, it does not work. If a user does not select any tags, we should treat it like any transaction can be returned even if it does not have any tags selected.

I am lost at how to query a many to many field with a empty list.

2

Hi,

One very simple approach can be using if/else statements (a sample approach in case of class-based view):

def get_queryset(self):
  tag = self.request.GET.get('tag', None)
  if tag:
    Transaction.objects.filter(transaction_category__master_category__category_type="Income", transaction_date__range=[start_date, end_date], tag__in=tags).filter(exclude_reimbursable).order_by().annotate(month=ExtractMonth("transaction_date"), year=ExtractYear("transaction_date")).values("month", "year").annotate(month_year=Concat("month", V(" "), "year", output_field=CharField()), month_total=Sum("amount")).values("month_year", "month_total").order_by("year", "month")
  else:
    # Your original query

I think this naive approach does it until someone comes with a better approach.

3

Thanks! I had thought about that, but this is just one of the many queries that are needed, so it would result in lots of if else.

4

You could try this filtering logic:

if tags:
    # If tags are provided, filter transactions that have ANY of the selected tags
    query = query.filter(tag__in=tags).distinct()
else:
    # If no tags are selected, include all transactions, even those without tags
    query = query.filter(Q(tag__isnull=True) | Q(tag__isnull=False)).distinct()

Then, you could call like this:

# If no tags are selected:
results = get_transactions(start_date, end_date)

# If tags are selected:
results = get_transactions(start_date, end_date, tags=selected_tags)

# If you want to include reimbursable transactions:
results = get_transactions(start_date, end_date, tags=selected_tags, exclude_reimbursable=False)