5

I am trying to use Django's ORM to generate a query using both extra and filter methods. Something like this: 

Model.objects.filter(clauseA).extra(clauseB).all()

This generates a query, but the issue is that everything in the filter clause is AND'd with everything in the extra clause, so the sql looks like: 

SELECT * FROM model WHERE clauseA AND clauseB.

My question is, is there a way to change the default combination operator for a query in Django such that the query generated will be:

SELECT * FROM model WHERE clauseA OR clauseB.
jcern
  • 7,798
  • 4
  • 39
  • 47
  • 2
    Are you just looking for the [or query?](http://stackoverflow.com/questions/739776/django-filters-or) (Possible duplicate) – Sayse Jul 16 '15 at 14:58
  • No, that only works for arguments within the filter method. I need to change the default combinator such that what gets returned from the `extra()` method is OR'd to what gets returned from the `filter()` method (instead of AND'd as it currently is). What I have in the extra method is native SQL that can't be represented in the Django ORM and can not be wrapped with `Q` – jcern Jul 16 '15 at 15:09
  • Is `Model.objects.filter(Q(clauseA) | Q(clauseB)).all()` not a valid solution? I can't see why you need the separate call (from my own testing this filter shown here will produce your desired sql output). I'm sure it might be possible to change the default combinator but I wouldn't advise it – Sayse Jul 16 '15 at 15:11
  • No, unfortunately that will not work. The reason that I need to use the extra clause is that I am working with some Postgres specific functions that don't translate into the ORM. As such can not be wrapped in Q to be passed into the filter method. – jcern Jul 16 '15 at 15:16
  • 2
    OP wants to know how to connect the `extra()` clause with an or instead of an and. Why he wants to do that is irrelevant for the question. – x squared Jul 16 '15 at 15:17
  • I dont believe there is a built in way to achieve this with the ORM. However, here is a thought - May be you can create a manager that accepts the clauses, and generates the query filter using the `Q` objects ? `.raw(..)` would be another option if you are dealing with the `extra` clause - again I have never tried this myself, but just a thought. – karthikr Jul 16 '15 at 15:36

2 Answers2

5

Try Q object

Model.objects.filter(Q(clauseA) | ~Q(clauseB))

EDIT

try this

Model.objects.filter(clauseA) | Model.objects.extra(clauseB)
suhain
  • 186
  • 1
  • 4
  • 1
    Thank you, the edit was exactly what I was looking for. I didn't realize you could chain `extra` and `filter` methods with `&` and `|`. – jcern Jul 16 '15 at 18:19
0

It might be easier if you just get rid of the filter clause, and include that filter directly into extra OR'd with your Postgres specific function. I think it is already a limitation of the Django ORM.

You can attempt to create your own Func expression though. Once you have created one for your Postgres specific function, you might be able to use a combination of Func(), F(), and Q() objects to get rid of that nasty .extra() function and chain them nicely.

Wannabe Coder
  • 1,457
  • 12
  • 21