How to use SQLAlchemy to select value at a position in a PostgreSQL ARRAY?

Question

For a nested comment system, I am storing a tree structure in a PostgreSQL table using the following definition in the Comment model:

path = sa.Column(ARRAY(sa.Integer))

This stores all the IDs of the comments in the path to the current one. So if a comment with id of 15 is the child of 11 and 13, then its path will be [11,13,15].

Now I want to find and count the child comments. So in order to find all the children of comment 11, including itself I want to generate SQL that looks like:

SELECT id, path FROM comments WHERE path[1] = 11;

On my database this happily returns a nice set of comment 11 and its children.

With the example above, if I want to grab the children of comment I can use the following SQL:

SELECT id, path FROM comments WHERE path[1] = 11 AND path[2] = 13;

How should SQLAlchemy be used to generate this SQL?

I have hacked around with filter_by() and filter() without much success... Neither of these work:

q = Comment.query.filter(path[1] == 11)
> NameError: name 'path' is not defined

q = Comment.query.filter_by(path[1] = 11)
> SyntaxError: keyword can't be an expression

I would prefer not to write custom SQL for this query, but if it's the only way, then any please give some pointers as to how it should be best constructed.

Edit 1

Confirming that I'm using SQLAlchemy 0.7 - so answers that work (around) with this version would be extra helpful.

I realised that for the comment implementation above, it's not necessary to find id numbers in the exact position, finding them at any position could work too. So any input on how to create SQLAlchemy to generate something like this would be good:

SELECT id, path FROM comments WHERE 11 = ANY (path);

Is that possible?

Answer 1

This will only work starting from SQLAlchemy 0.8 (beta2 at the moment of writing this):

from sqlalchemy.dialects import postgresql

class Comment(Base):
    __tablename__ = 'comments'
    id = Column(Integer, primary_key=True)
    path = Column(ARRAY(Integer))

q = Comment.query.filter(Comment.path[1] == 11)
# This is just to demonstrate how compiled query would look like.
print q.statement.compile(dialect=postgresql.dialect())

The output:

SELECT comments.id, comments.path 
FROM comments 
WHERE comments.path[%(path_1)s] = %(param_1)s

---

Update

There was recently a discussion in SA mailing list about how to create = ANY (and related) queries for Postgres arrays. Even though the examples there are written for 0.8, it should somewhat work in 0.7 as well. Here's a working implementation of = ANY for 0.7 (tested):

from sqlalchemy.sql import literal, tuple_

# There's already built-in any() function in Python, we don't want to
# shadow that.
def any_(value, col):
    return literal(value).op('= ANY')(tuple_(col))

q = Comment.query.filter(any_(11, Comment.path))