16

Let's say I have two tables in Django, TableA and TableB. Table A contains some boolean field, bool, and TableB contains a foreign key field, for_field to TableA, which can be Null.

class TableA(models.Model):
    bool = models.BooleanField()

class TableB(models.Model):
    for_field = models.ForeignKey('TableA', null=True)

If I want to filter TableB so as to get all the entries where for_field.bool is True or for_field is Null, what is the shortest way to achieve this?

I'm using .filter((Q(for_field__is_null=True) | Q(for_field__bool=True)), but I wonder if there's shorter code for this.

mathiascg
  • 550
  • 1
  • 5
  • 15
  • 1
    Have you tried `.exclude(for_field__bool=False)`? Is this giving same results? – valignatev Jul 28 '16 at 16:35
  • I thought of that, but I'm not sure whether django will raise an error if trying to access `bool` in a `Null` foreign key field. – mathiascg Jul 28 '16 at 16:37
  • 3
    A minor improvement: `Q(for_field__is_null=True)` is equivalent to `Q(for_field=None)`. – knbk Jul 28 '16 at 16:38

2 Answers2

15

After some experiments it seems that .exclude(for_field__bool=False) will contain also for_field__isnull=True entries and will not raise any exceptions. You can be sure by executing .exclude(for_field__bool=False).filter(for_field__isnull=True) and see some results also.

And honestly I don't know which option is faster, but IMO your variant with two Q objects much more readable because it shows logic you're really want. So I actually suggest you to stick with it.

valignatev
  • 6,020
  • 8
  • 37
  • 61
  • Thanks! This will probably do. I agree that showing the underlying logic makes the code more readable in most cases, but in this case I have many similar `for_field`s I need to concatenate in the filter query, so it would produce a really long and ugly line of code. – mathiascg Jul 28 '16 at 16:51
  • The `exclude` version is nice if you have many fields, because you can chain them. `exclude(some_field=False).exclude(some_other_field=False)` etc. – Peter DeGlopper Jul 28 '16 at 16:59
  • **FieldError at /.../ Unsupported lookup 'bool' for BooleanField or join on the field not permitted.** when using bool on a foreign key filtering. – AnonymousUser Feb 18 '22 at 04:32
3

I'm pretty sure, that your option is the shortest possible (correct me if I'm wrong). That is because you can't do OR queries without Q objects.

JustLive
  • 91
  • 4