SQLAlchemy query using contains, array casting and subqueries

Question

Any assistance turning the following query into a SQLAlchemy query would be greatly appreciated:

select * from app where (select app_ids from user where user_id=12345) @> array[app_id];

app_id in app is an int column
app_ids in user is an int[] column
user_id in user is an int column

I've tried a number of formulations, but all fail for one reason or another, here is an example that fails because ARRAY objects don't have a contained_by method:

subq = self.session.query(objects.User.app_ids).filter(objects.User.user_id == 12345).subquery('subq')

self.session.query(objects.App).filter(postgresql.ARRAY(objects.App.app_id).contained_by(subq.c.app_ids)).all()

I've also tried the following equivalent raw queries without any luck. Maybe these will be easier to translate though?

select * from app where array[app_id] <@ (select app_ids from user where user_id=12345);


select * from app where app_id=ANY((select app_ids from user where user_id=12345)::integer[]);

All of these raw queries work fine in postgres.

Thanks.

fizloki · Accepted Answer · 2015-09-03 17:17:13Z

2

Figured it out for the any() formulation, which turned out to be the best for any large number of entries:

subq = session.query(objects.User.app_ids).filter(objects.User.user_id == 12345)

results = session.query(objects.App).filter(
   objects.App.id == sqlalchemy.func.any(sqlalchemy.cast(subq.as_scalar(), postgressql.ARRAY(sqlalchemy.Integer))
)

answered Sep 3, 2015 at 17:17

fizloki

1503 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy query using contains, array casting and subqueries

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related