Including Duplicate Tables using Django's ORM Extra()

Question

I'm trying to implement a simple triplestore using Django's ORM. I'd like to be able to search for arbitrarily complex triple patterns (e.g. as you would with SparQL).

To do this, I'm attempting to use the .extra() method. However, even though the docs mention it can, in theory, handle duplicate references to the same table by automatically creating an alias for the duplicate table references, I've found it does not do this in practice.

For example, say I have the following model in my "triple" app:

class Triple(models.Model):
    subject = models.CharField(max_length=100)
    predicate = models.CharField(max_length=100)
    object = models.CharField(max_length=100)

and I have the following triples stored in my database:

subject predicate object
bob has-a hat .
bob knows sue .
sue has-a house .
bob knows tom .

Now, say I want to query the names of everyone bob knows who has a house. In SQL, I'd simply do:

SELECT t2.subject AS name
FROM triple_triple t1
INNER JOIN triple_triple t2 ON
    t1.subject = 'bob'
AND t1.predicate = 'knows'
AND t1.object = t2.subject
AND t2.predicate = 'has-a'
AND t2.object = 'house'

I'm not completely sure what this would look like with Django's ORM, although I think it would be along the lines of:

q = Triple.objects.filter(subject='bob', predicate='knows')
q = q.extra(tables=['triple_triple'], where=["triple_triple.object=t1.subject AND t1.predicate = 'has-a' AND t1.object = 'house'"])
q.values('t1.subject')

Unfortunately, this fails with the error "DatabaseError: no such column: t1.subject"

Running print q.query shows:

SELECT "triple_triple"."subject" FROM "triple_triple" WHERE ("triple_triple"."subject" = 'bob' AND "triple_triple"."predicate" = 'knows'
AND triple_triple.object = t1.subject AND t1.predicate = 'has-a' AND t1.object = 'house')

which appears to show that the tables param in my call to .extra() is being ignored, as there's no second reference to triple_triple inserted anywhere.

Why is this happening? What's the appropriate way to refer to complex relationships between records in the same table using Django's ORM?

EDIT: I found this useful snippet for including custom SQL via .extra() so that it's usable inside a model manager.

Answer 1

I think what you're missing is the select parameter (for the extra method)

This seems to work:

qs = Triple.objects.filter(subject="bob", predicate="knows").extra(
          select={'known': "t1.subject"},
          tables=['"triple_triple" AS "t1"'],
          where=['''triple_triple.object=t1.subject 
                    AND t1.predicate="has-a" AND t1.object="'''])

qs.values("known")