Django LEFT JOIN?

Question

I have models, more or less like this:

class ModelA(models.Model):
    field = models.CharField(..)

class ModelB(models.Model):
    name = models.CharField(.., unique=True)
    modela = models.ForeignKey(ModelA, blank=True, related_name='modelbs')

    class Meta:
        unique_together = ('name','modela')

I want to do a query that says something like: "Get all the ModelA's where field name equals to X that have a ModelB model with a name of X OR with no model name at all"

So far I have this:

ModelA.objects.exclude(field=condition).filter(modelsbs__name=condition)

This will get me all the ModelAs that have at least one modelB (and in reality it will ALWAYS be just one) - but if a ModelA has no related ModelBs, it will not be in the result set. I need it to be in the resultset with something like obj.modelb = None

How can I accomplish this?

Answer 1

Use Q to combine the two conditions:

from django.db.models import Q
qs = ModelA.objects.exclude(field=condition)
qs = qs.filter(Q(modelbs__name=condition) | Q(modelbs__isnull=True))

To examine the resulting SQL query:

print qs.query.as_sql()

On a similar query, this generates a LEFT OUTER JOIN ... WHERE (a.val = b OR a.id IS NULL).

Answer 2

LEFT JOIN is a union of two queries. Sometimes it's optimized to one query. Sometimes, it is not actually optimized by the underlying SQL engine and is done as two separate queries.

Do this.

for a in ModelA.objects.all():
    related = a.model_b.set().all()
    if related.count() == 0:
        # These are the A with no B's
    else:
        # These are the A with some B's

Don't fetishize about SQL outer joins appearing to be a "single" query.

Answer 3

It looks like you are coming up against the 80% barrier. Why not just use .extra(select={'has_x_or_none':'(EXISTS (SELECT ...))'}) to perform a subquery? You can write the subquery any way you like and should be able to filter against the new field. The SQL should wind up looking something like this:

SELECT *, 
  ((EXISTS (SELECT * FROM other WHERE other.id=primary.id AND other.name='X'))
    OR (NOT EXISTS (SELECT * FROM other WHERE other.id=primary.id))) AS has_x_or_none
  FROM primary WHERE has_x_or_none=1;

Answer 4

you can do it with a reference eitherway