PostgreSQL syntax error when using with django-ordered-model

[taobojlen]

I'm trying to use django-tree-queries in combination with django-ordered-model. That library provides some useful helper methods for ordering, like methods to move a record to a different place in the ordering, or to swap two records. I don't think that there's any reason these two libraries should be incompatible.

The issue I'm running into is that django-tree-queries is generating invalid SQL. My model looks something like this:

class PolicyQuerySet(TreeQuerySet, OrderedModelQuerySet):
    pass

class PolicyManager(TreeManager):
    with_tree_queries = True

    def get_queryset(self):
        # Override get_queryset to return a custom queryset that combines
        # tree queries and ordered model queries
        return PolicyQuerySet()

class Policy(TreeNode, OrderedModel):
    name = models.TextField()

    # the following is actually provided implicitly by OrderedModel
    order = models.PositiveIntegerField()
    class Meta:
        ordering = ("order",)

When I try to execute queries I get a SQL syntax error. The generated SQL looks like, for example

            WITH RECURSIVE __tree (
                "tree_depth",
                "tree_path",
                "tree_ordering",
                "tree_pk"
            ) AS (
                SELECT
                    0 AS tree_depth,
                    array[T.id] AS tree_path,
                    array[order] AS tree_ordering,
                    T."id"
                FROM stratus_policy T
                WHERE T."parent_id" IS NULL

                UNION ALL

                SELECT
                    __tree.tree_depth + 1 AS tree_depth,
                    __tree.tree_path || T.id,
                    __tree.tree_ordering || order,
                    T."id"
                FROM stratus_policy T
                JOIN __tree ON T."parent_id" = __tree.tree_pk
            )
            SELECT (__tree.tree_depth) AS "tree_depth", (__tree.tree_path) AS "tree_path", (__tree.tree_ordering) AS "tree_ordering", "stratus_policy"."id", "stratus_policy"."order", "stratus_policy"."parent_id", "stratus_policy"."name" FROM "stratus_policy" , "__tree" WHERE ("stratus_policy"."parent_id" IS NULL AND (__tree.tree_pk = stratus_policy.id)) ORDER BY ("__tree".tree_ordering) ASC

The problem is with array[order] AS tree_ordering. Postgres throws a syntax error on this part.

Am I doing anything obviously wrong here?

[matthiask]

The problem is probably that the order field name isn't quoted correctly. Since order is a SQL keyword the SQL should use a quoted variant ("order") instead. Respectively we should just always apply quoting to the field name.

Do you want to attempt to fix this?

(A workaround would be to use a different name for the field, for example ordering or position or something.)

[taobojlen]

Thanks @matthiask ! Yes, I'll give it a go.