Related Field got invalid lookup :exists

Question

In my database, I have a Photo class, that I use as a foreignkey to other classes. These are two examples of such.

class SubstancePointsLabel(ResultBase):   
    #: photo being labeled
    photo = models.ForeignKey(Photo, related_name='substance_points_labels')

class SubstancePoint(EmptyModelBase):   
    #: photo
    photo = models.ForeignKey(Photo, related_name='substance_points')

The only difference in these instances the ResultBase and EmptyModelBase but nothing in seems (to me) to be the cause.

class EmptyModelBase(models.Model):
    """ Base class of all models, with no fields """

    def get_entry_dict(self):
        return {'id': self.id}

    def get_entry_attr(self):
        ct = ContentType.objects.get_for_model(self)
        return 'data-model="%s/%s" data-id="%s"' % (
            ct.app_label, ct.model, self.id)

    def get_entry_id(self):
        ct = ContentType.objects.get_for_model(self)
        return '%s.%s.%s' % (ct.app_label, ct.model, self.id)

    class Meta:
        abstract = True
        ordering = ['-id']

class ResultBase(UserBase):
    """ Base class for objects created as a result of a submission """

    #: The MTurk Assignment that the user was in when this record was created
    mturk_assignment = models.ForeignKey(
        'mturk.MtAssignment', null=True, blank=True,
        related_name='+', on_delete=models.SET_NULL
    )

    #: True if created in the mturk sandbox
    sandbox = models.BooleanField(default=False)

    #: This has been marked by an admin as invalid or incorrect AND will be
    #: re-scheduled for new labeling.  This can happen when an experiment is
    #: changed and things need to be recomputed.
    invalid = models.BooleanField(default=False)

    #: The method by which the quality label was obtained
    QUALITY_METHODS = (
        ('A', 'admin'), ('C', 'CUBAM'),
        ('M', 'majority vote'), ('Q', 'qualification'),
        ('S', 'seed point'),
    )
    quality_method_to_str = dict((k, v) for (k, v) in QUALITY_METHODS)
    #: The method by which the quality label was obtained
    quality_method = models.CharField(
        max_length=1, choices=QUALITY_METHODS, blank=True, null=True)

    #: time taken to specify this label in ms
    time_ms = models.IntegerField(blank=True, null=True, db_index=True)

    #: time taken to specify this label in ms, excluding time that the user was
    #: in another window
    time_active_ms = models.IntegerField(blank=True, null=True, db_index=True)

    #: payment for this specific label
    reward = models.DecimalField(
        decimal_places=4, max_digits=8, null=True, blank=True)

    def save(self, *args, **kwargs):
        if not self.id and self.mturk_assignment:
            self.sandbox = self.mturk_assignment.hit.sandbox
        if not self.reward:
            from common.utils import compute_label_reward
            self.reward = compute_label_reward(self)
        super(ResultBase, self).save(*args, **kwargs)

    def time_s(self):
        """ Time pretty-printed in seconds (helper for templates) """
        if self.time_ms:
            t = self.time_ms / 1000.0
            return round(t, 2 if t < 10 else 1)
        else:
            return None

    def time_active_s(self):
        """ Active pretty-printed time in seconds (helper for templates) """
        if self.time_active_ms:
            t = self.time_active_ms / 1000.0
            return round(t, 2 if t < 10 else 1)
        else:
            return None

    def mark_invalid(self, save=True):
        dirty = (not self.invalid)
        self.invalid = True
        self.quality_method = 'A'
        if save:
            self.save()
        if dirty:
            marked_invalid.send(sender=self.__class__, instance=self)

    def get_thumb_overlay(self):
        return self.__unicode__()

    class Meta:
        abstract = True
        ordering = ['-time_ms']

For a Photo instance, I'm capable of accessing SubstancePointsLabel and SubstancePoint via the related name.

In : photo.substance_points_labels.count()
Out: 3

In : photo.substance_points.count()
Out: 9

I wish to filter Photo instances, on whether the related class exists, which I do as follows.

In : Photo.objects.filter(substance_exists_labels__exists=True).count()
Out: 335932

Now, as above, this works for some. For a few others, the query returns an error:

In : Photo.objects.filter(substance_points__exists=True).count()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/data_nfs/opensurfaces2/server/matclass/tasks.pyc in <module>()
----> 1 Photo.objects.filter(substance_points__exists=True).count()

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/manager.pyc in manager_method(self, *args, **kwargs)
     90         def create_method(name, method):
     91             def manager_method(self, *args, **kwargs):
---> 92                 return getattr(self.get_queryset(), name)(*args, **kwargs)
     93             manager_method.__name__ = method.__name__
     94             manager_method.__doc__ = method.__doc__

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/query.pyc in filter(self, *args, **kwargs)
    689         set.
    690         """
--> 691         return self._filter_or_exclude(False, *args, **kwargs)
    692 
    693     def exclude(self, *args, **kwargs):

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/query.pyc in _filter_or_exclude(self, negate, *args, **kwargs)
    707             clone.query.add_q(~Q(*args, **kwargs))
    708         else:
--> 709             clone.query.add_q(Q(*args, **kwargs))
    710         return clone
    711 

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/sql/query.pyc in add_q(self, q_object)
   1329         existing_inner = set(
   1330             (a for a in self.alias_map if self.alias_map[a].join_type == self.INNER))
-> 1331         clause, require_inner = self._add_q(where_part, self.used_aliases)
   1332         self.where.add(clause, AND)
   1333         for hp in having_parts:

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/sql/query.pyc in _add_q(self, q_object, used_aliases, branch_negated, current_negated)
   1356                 child_clause, needed_inner = self.build_filter(
   1357                     child, can_reuse=used_aliases, branch_negated=branch_negated,
-> 1358                     current_negated=current_negated, connector=connector)
   1359                 joinpromoter.add_votes(needed_inner)
   1360             target_clause.add(child_clause, connector)

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/sql/query.pyc in build_filter(self, filter_expr, branch_negated, current_negated, can_reuse, connector)
   1223             # refactored when composite fields lands.
   1224             condition = field.get_lookup_constraint(self.where_class, alias, targets, sources,
-> 1225                                                     lookups, value)
   1226             lookup_type = lookups[-1]
   1227         else:

/data_nfs/opensurfaces2/venv/local/lib/python2.7/site-packages/django/db/models/fields/related.pyc in get_lookup_constraint(self, constraint_class, alias, targets, sources, lookups, raw_value)
   1575                 root_constraint.add(value_constraint, OR)
   1576         else:
-> 1577             raise TypeError('Related Field got invalid lookup: %s' % lookup_type)
   1578         return root_constraint
   1579 

TypeError: Related Field got invalid lookup: exists

The classes are created the same way, and are queried the same way. What could the cause it's giving an error for some instances, and not for others?

Answer 1

You can do it using exclude method and __isnull lookup.

Photo.objects.exclude(substance_points__isnull=True).count()

---

Or you can use conditional expression

from django.db.models import When, Case, IntegerField

Photo.objects.annotate(
   has_substance_points=Case(
       When(
          substance_points__isnull=True, then=0
       ), 
       default=1, 
       output_field=IntegerField()
   )
).filter(has_substance_points=1).count()

Above query will return count of photos that has substance_points

---

UPDATE

Alternatively, If you just want count of photos that has substance_points, you can also think of below approach.

SubstancePoint.objects.all().order_by('photo_id').distinct('photo_id').count()
# Query will return count of photos that has substance_points

Answer 2

Is there really something like "__exists" in field lookups in Django?? There's an easy way to do the same by using "__isnull". Instead of:

Photo.objects.filter(substance_points__exists=True).count()

you could:

Photo.objects.filter(substance_points__isnull=False).count()