Suppose I have the following very simple tree structure represented by nodes in a table:
name = models.CharField(max_length=50)
flags = models.CharField(max_length=50)
parent = models.ForeignKey('self')
What I'd like to know is, how can I emulate the following SQL statement in Django?
SELECT parent.name, child.name
FROM Nodes as child
JOIN Nodes as parent ON child.parent == parent.id
WHERE parent.flags == 'STRING1' AND child.flags == 'STRING2';
The closest I've gotten seems very inefficient:
for parent in Nodes.objects.filter(flags='STRING1'):
for child in Nodes.objects.filter(flags='STRING2', parent=parent.id):
print parent.name, child.name
Can someone help me understand if there's a better way to execute this SQL query?
for node in Nodes.objects.filter(flags='STRING2', parent__flags='STRING1'): print node.parent.name, node.name