Fields using db_column are unsupported

[joshbrooks]

Given an M2M model which uses a db_column, the SQL code generated by django_cte does not account for the column name. I'd expect a CTE join to join on oid in the example below as that is the db_column value. But the join is specified on treenode_id.

The issue arises, I think, here:

django-cte/django_cte/meta.py

Lines 61 to 68 in 61d5311

	def as_sql(self, compiler, connection):
	qn = compiler.quote_name_unless_alias
	ref = self._ref
	if isinstance(ref, Col) and self.name == "pk":
	column = ref.target.column
	else:
	column = self.name
	return "%s.%s" % (qn(self.table_alias), qn(column)), []

class TreenodeUserClosure(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, db_column="uid")
    treenode = models.ForeignKey(TreeNode, db_column="oid")

The code generated by django_cte does not account for the db_column

cte = With(
    TreenodeUserClosure.objects.values("treenode_id").filter(user_id=uid)
)
cte.join(CouchDocCte, treenode_id=cte.col.treenode_id).with_cte(cte)

Raises

ProgrammingError: column cte.treenode_id does not exist
LINE 1: ..." INNER JOIN "cte" ON "couchdoc"."treenode_id" = ("cte"."tre...
                                                             ^

The code generated by the queryset was:

In [3]: TreenodeUserClosureCte.joined(491).query.sql_with_params()
Out[3]: 
('WITH RECURSIVE "cte" AS (SELECT "userorgs"."oid" FROM "userorgs" WHERE "userorgs"."uid" = %s) SELECT "couchdoc"."rid", "couchdoc"."clocked_at", "couchdoc"."received_at", "couchdoc"."replaced_at", "couchdoc"."replaced_by", "couchdoc"."deleted_at", "couchdoc"."user_id", "couchdoc"."treenode_id", "couchdoc"."meta", "couchdoc"."attachments", "couchdoc"."odkresponse" FROM "couchdoc" INNER JOIN "cte" ON "couchdoc"."treenode_id" = ("cte"."treenode_id")',
 (491,))

[joshbrooks]

I can work around the issue with an alias

cte = With(
    cls.objects.annotate(node=F('treenode_id')).values("node").filter(user_id=uid)
)
cte.join(CouchDocCte, treenode_id=cte.col.node).with_cte(cte)

This will use the alias node correctly in the join

[millerdev]

That's a good workaround.

I wonder if CTEColumn.as_sql could use ref.target.column like it does for "pk"? I think the challenge will be detecting when it is appropriate to use that vs using self.name. Not sure when I will have time to work on this, but pull requests are welcome 😃

---

For reference, here's a formatted version of the SQL from above with a comment detailing what was expected.

WITH RECURSIVE "cte" AS (
  SELECT "userorgs"."oid"
  FROM "userorgs"
  WHERE "userorgs"."uid" = %s
)
SELECT "couchdoc"."rid", ...
FROM "couchdoc"
INNER JOIN "cte" ON "couchdoc"."treenode_id" = ("cte"."treenode_id")
-- expected:        "couchdoc"."treenode_id" = ("cte"."oid")