Postgres not(any_) issue within recursive CTE

[cled...@twistbioscience.com]

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

[Mike Bayer]

On 07/20/2016 07:53 PM, cled...@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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.