CODEWITHSUNDEEP
djangodjango-queryset
DanEEStar
DanEEStar

Reputation: 6280

add raw sql where clause to django queryset

Is it possible to add an additional raw sql clause to a django queryset? Preferably with the RawSQL clause to a normal queryset. It should be a normal queryset and not a rawqueryset because I want to use it in the django admin.

In my particular case I want to add an additonal exists where clause:

and exists (
   select 1
   from ...
)

In my concrete case I have two models Customer and Subscription. The Subscription has a start and optional end date field.

I want to have a queryset with all customers which have a current subscription today. Like this SQL query:

select *
from customers_customer c
where exists (
  select 1
  from subscriptions_subscription sc
  where sc.customer_id = c.id
  and sc.start < current_date
  and (sc.end is null or sc.end > current_date)
)

I was not able to make a queryset out of this. The best thing I arrived, was something like:

    cs = Customer.objects.annotate(num_subscriptions=RawSQL(
        '''
        select count(sc.id)
        from subscriptions_customersubscription sc
        where sc.customer_id = customers_customer.id
        and sc.start < current_date
        and (sc.end is null or sc.end > current_date)
        ''', []
    ))

But this query does not perform as well as the SQL-query with the where exists.

Upvotes: 1

Views: 2164

Answers (2)

zakum1
zakum1

Reputation: 1112

In answer to the original question, one can use the QuerySet.extra() method to add raw SQL as explained in the docs

Upvotes: 0

vsd
vsd

Reputation: 1473

Not answering your question, but you can query customers like this:

from django.db.models import Q

Customer.objects.filter(
    Q(subscription__start__lt=current_date),
    Q(subscription__end=None) | Q (subscription__end__gt=current_date)
).distinct()

Upvotes: 2

Related Questions