Intersection between TreeQuerySet and QuerySet

[gevezex]

I am trying to find an intersection between objects of a TreeNode model and the objects of a normal django model who has a FK to this TreeNode model. Models look like this:

class FolderTree(TreeNode):
    name = models.CharField(max_length=255)
    fullpath = models.CharField(max_length=255, null=False, blank=False, unique=True)
    isfile = models.BooleanField(default=False, null=False, blank=True)
    hasdoc = models.BooleanField(default=False, null=False, blank=True)
    document =  models.ForeignKey(Document, null=True, blank=True, related_name='folder', on_delete=models.PROTECT)
    file_extension = models.CharField(max_length=10, null=True, blank=True)
    deleted = models.BooleanField(default=False, null=False, blank=True)
    descendant_files = models.IntegerField(null=True, blank=True)

    class Meta:
        ordering = ("name",)
        unique_together = (("name", "parent"),)

    def __str__(self):
        return self.name

class DossierFile(models.Model):
    user = models.ForeignKey(User, default=1, on_delete=models.PROTECT, null=False, blank=False)
    dossier = models.ForeignKey(Dossier, default=1, on_delete=models.PROTECT, null=False, blank=False)
    file = models.ForeignKey(FolderTree, default=1, on_delete=models.PROTECT, null=False, blank=False, related_name='dossierfiles')
    created_at = models.DateTimeField(auto_now_add=True)

What I want is to have an intersection of all the objects of DossierFile's file fields and the descendants of the FolderTree objects. I do it like this:

f = FolderTree.objects.get(pk=21)
f_desc = f.descendants().values_list('id', flat=True)
all_dossier_files = DossierFile.objects.all().values_list('file', flat=True)
all_dossier_files.intersection(f_desc)

f_desc is an QuerySet and all_dossier is a TreeQuerySet obviousely. The error I get is:

InvalidColumnReference                    Traceback (most recent call last)
~/anaconda3/envs/lblmaker/lib/python3.8/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85

InvalidColumnReference: ORDER BY position 2 is not in select list
LINE 51: ..."file_id" FROM "casemaker_dossierfile") ORDER BY (2) ASC LIM...
                                                              ^

The above exception was the direct cause of the following exception:

Any clue what is happening or how to solve this?

[matthiask]

I'm a bit confused that file is a folder :)

But apart from that, I'm not very surprised that this doesn't work. django-tree-queries doesn't understand the more intricate parts of the ORM so that's probably the reason for the failure here.

Maybe all_dossier_files = DossierFile.objects.filter(file__in=f.descendants()) or something would work for you? And if it doesn't maybe this variant is acceptable: all_dossier_files = DossierFile.objects.filter(file__in=list(f.descendants().values_list("id", flat=True)) but of course, if the list of IDs is too large you'll get performance problems. When that happens you can always drop down to raw SQL or something.

[gevezex]

Haha that is indeed confusing, but how we use it is that objects in FolderTree can be a file (isfile=True) or folder.

Thnx for your quick answer. I will try it on a big db as locally your second suggestion works well but my test db is not that big. So I will apply it on our big db later on this week.