Django combine filter on two fields

Question

I am relatively new to Django. I'm having problem when filtering data. I have two models, given below:

Account(models.Model):
    name = models.CharField(max_length=60)
    hotel = models.ForeignKey(Hotel)
    account_type = models.CharField(choices=ACCOUNT_TYPE, max_length=30)

Transaction(models.Model):        
    account = models.ForeignKey(Account, related_name='transaction')
    transaction_type = models.CharField(choices=TRANSACTION_TYPE, max_length=15)
    description = models.CharField(max_length=100, blank=True, null=True)
    date_created = models.DateTimeField(default=timezone.now)

ACCOUT_TYPE is:

ACCOUNT_TYPE = (
  (0, 'Asset'),
  (1, 'Liabilities'),
  (2, 'Equity'),
  (3, 'Income'),
  (4, 'Expense')
)

I want to filter all the transactions where the account type is Income and Expense within a given date range. How can I combine those filters in Django?

I have tried like this:

income_account = Account.objects.filter(account_type=3)
expense_account = Account.objects.filter(account_type=4)
transactions = Transaction.objects.filter(Q(
  account=income_account,
  date_created__gte=request.data['start_date'],
  date_created__lte=request.data['end_date']
) & Q(
  account=expense_account,
date_created__gte=request.data['start_date'],
date_created__lte=request.data['end_date'])).order_by('date_created')

But it's not working. It raises the following error:

  ProgrammingError: more than one row returned by a subquery used as an expression

Solution 1

income_account and expense_account is not single object, it is a list of objects. So instead of this account=income_account and this account=expense_account try to use in: account__in=income_account and account__in=expense_account. Also you probably could simplify queryset like this:

accounts = Account.objects.filter(Q(account_type=3) | Q(account_type=4))
transactions = Transaction.objects.filter(
    account__in=accounts,
    date_created__gte=request.data['start_date'],
    date_created__lte=request.data['end_date']
).order_by('date_created')

Solution 2

Instead of having multiple querysets, you can have only one, as Q allows ORing of filters. You could do:

Transaction.objects.filter(
    (Q(account__account_type=3) | Q(account__account_type=4)) &
    Q(date_created__range=[start_date, end_date])
)

The __range can be used to get dates between the specified start_date and end_date.

Solution 3

You can always use in to lookup records by multiple values. So, if you want Transaction where ACCOUNT_TYPE are Income, Expenseyou can use it like this.

Transaction.objects.filter(Q(account__in=[3,4]) & Q(date_created__gte=request.data['start_date']) & Q(date_created__lte=request.data['end_date'])).order_by('date_created')