Hi,
I'm very glad to see the any_ operator fully supported in SqlAlchemy 1.1b2.
We'd like to use this operator to implement an efficient recursive CTE
(RCTE) that avoids cycles. One way to get an RCTE to avoid cycles is to
maintain an array of visited nodes or links, then to check for membership
of the current node / link within that array as a recursion stop condition.
We're using the following SqlAlchemy construct:
str(not_(Link.id == any_(valid_parents.c.visited_ids)))
It seems to generate the following SQL:
link.id != ANY (valid_parents.visited_ids)
(using str(q.statement.compile(dialect=postgresql.dialect())))
That SQL is not sufficient to cause the RCTE to avoid cycles. It appears
to just keep recursing.
With the following small adjustment to the generated SQL:
NOT link.id = ANY (valid_parents.visited_ids)
We successfully get the RCTE to avoid cycles.
Is there any way to force SqlAlchemy to use that style of negative logic,
so that Postgres picks up the NOT operator instead of != ? (assuming
that's the right solution of course!)
Here is some basic code:
class Link(db.Model):
__tablename__ = 'link'
id = Column(Integer, primary_key=True)
valid_parents = db.query(array([-1]).label('visited_ids'))
rep = str(not_(Link.id == any_(valid_parents.c.visited_ids)))
print rep
Thanks very much for any guidance, and thanks for all the great Postgres
support in the new version!
Charlie Ledogar
Twist Bioscience
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
