CODEWITHSUNDEEP
djangodjango-queryset
Md. Tanvir Raihan
Md. Tanvir Raihan

Reputation: 4285

Django combine filter on two fields

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

Upvotes: 5

Views: 21985

Answers (4)

Piyush S. Wanare
Piyush S. Wanare

Reputation: 4933

This will work for you:-

result = Account.objects.filter((account_type__in['Income','Expense'])
                   OR
result = Account.objects.filter((account_type__in['0','4'])

I have put 0 and 4 as string because you have mention account_type as CharField.

Upvotes: 1

Evans Murithi
Evans Murithi

Reputation: 3257

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.

Upvotes: 3

Rajesh Yogeshwar
Rajesh Yogeshwar

Reputation: 2179

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')

Upvotes: 2

neverwalkaloner
neverwalkaloner

Reputation: 47354

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')

Upvotes: 6

Related Questions