Union Error

[João Pedro]

When performing a TreeQuerySet union I get an error message:

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

Is it a bug innate to the TreeQuerySet?

The model is :

class TreeAbstract(MPTTModel):

    system = models.ForeignKey(System, on_delete = models.CASCADE)

    parent = TreeForeignKey('self', null=True, blank=True, related_name='children', on_delete = models.CASCADE)

    name = models.CharField(max_length = 50)

    quantity = models.IntegerField(default = 1)

    image = models.ImageField(default="default.jpg", upload_to="pbs_pics")

    description = models.TextField(max_length = 250, null = True, blank = True)

    slug = models.SlugField(unique=True)

   

    class Meta:

        abstract = True

    def next_level(self):

        return self.level + 1 

    def __str__(self):

        return self.name

class ProjectBreakdownStructure(TreeAbstract):

    project = models.ForeignKey(Project, on_delete = models.CASCADE)

    original_node = models.ForeignKey(ProjectBreakdownStructureTemplate, null = True, on_delete = models.CASCADE)

    def _get_unique_slug(self):

        slug = slugify(self.name)

        unique_slug = slug

        num = 1

        while ProjectBreakdownStructure.objects.filter(slug=unique_slug).exists():

            unique_slug = '{}-{}'.format(slug, num)

            num += 1

        return unique_slug

    

    def full_path_to_node(self):

        return "/".join([node.slug for node in self.get_ancestors(include_self=True)])

    def save(self, *args, **kwargs):

        

        if not self.slug or slugify(self.name) != self.slug :

            self.slug = self._get_unique_slug()

        super().save(*args, **kwargs)

[m...@feinheit.ch]

Hi,

You should probably post not only models but also the union call and a full traceback of the exception. 

Matthias

[João Pedro]

Sorry for that, it's my bad. I'll give more details below:

Objective of the code:

Get list of tasks associated of a breakdown of a project

How it was planned:

There are templates that are trees:  ProjectBreakdownStructureTemplate . For each new user created Project , he can select a template and modify it by creating an instance of ProjectBreakdownStructure (that keeps a reference to its original template through the foreingkey Original_Node). Each  ProjectBreakdownStructureTemplate   has some Activities attached to it.

Why the union:

I want to get the total Activities for a Project. 

 

The "Project" model is quite simple, containing only name and description.

There is also a model called "Activity", containing only name and description too, which has a foreign key to the  "ProjectBreakdownStructureTemplate" .

The call is :

q1 = Project.objects.values_list('projectbreakdownstructure__name') #work by itself

q2 = ProjectBreakdownStructure.objects.values_list('original_node__activity__name') #work by itself

q1.union(q2) # Error

Traceback:

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 263, in __repr__

    data = list(self[:REPR_OUTPUT_SIZE + 1])

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 269, in __len__

    self._fetch_all()

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 1303, in _fetch_all

    self._result_cache = list(self._iterable_class(self))

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 142, in __iter__

    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1108, in results_iter

    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1143, in execute_sql

    sql, params = self.as_sql()

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 507, in as_sql

    result, params = self.get_combinator_sql(combinator, self.query.combinator_all)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 444, in get_combinator_sql

    raise DatabaseError('ORDER BY not allowed in subqueries of compound statements.')

[João Pedro]

Sorry I'll try to be clearer below:

Objective of the code: List all Activities related to a Project

How the code work: There is the ProjectBreakdownStructureTemplate (PBST) model that is a tree containing the breakdown of products in a project. For each Project , the user can select a number of this PBST and convert them into PBS (ProjectBreakdownStructure) . Each node in PBST can have none or multiple Activity objects associated to it.

Project <-> PBS  <-> PBST <-> Activity

Project is derived from models.Model with name and description fieldss derived from models.Model with name and description fields

Activity is derived from models.Model with name, description fields  and a ForeignKey to PBST

PBS and PBST both derive from an AbstractModel (meta : abstract = True) that uses MPTT 

The final objective is to have a list of Activities for a Project.

The calls:

>>> q1 =  Project.objects.values_list('projectbreakdownstructure__name') # Works fine

>>> q2 = PBS.objects.values_list('original_node__activity__name') # Works fine

>>> q1.union(q2) # Fails

Traceback:

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 263, in __repr__

    data = list(self[:REPR_OUTPUT_SIZE + 1])

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 269, in __len__

    self._fetch_all()

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 1303, in _fetch_all

    self._result_cache = list(self._iterable_class(self))

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\query.py", line 142, in __iter__

    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1108, in results_iter

    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1143, in execute_sql

    sql, params = self.as_sql()

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 507, in as_sql

    result, params = self.get_combinator_sql(combinator, self.query.combinator_all)

  File "C:\Users\ss7f0830\Documents\Projects\ctr-7-project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 444, in get_combinator_sql

    raise DatabaseError('ORDER BY not allowed in subqueries of compound statements.')

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

Em quarta-feira, 9 de setembro de 2020 às 11:48:32 UTC+2, m...@feinheit.ch escreveu: