On 07/20/2016 07:53 PM, cledo...@twistbioscience.com wrote:
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!)
try calling self_group() on the expression before applying not_() around it.
>>> str(not_((column('foo') == any_(column('bar'))).self_group()))
'NOT (foo = ANY (bar))'
Here is some basic code:
|
classLink(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)))
printrep
|
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
