CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
fizloki
fizloki

Reputation: 151

SQLAlchemy query using contains, array casting and subqueries

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];

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.

Upvotes: 0

Views: 3640

Answers (1)

fizloki
fizloki

Reputation: 151

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))
)

Upvotes: 2

Related Questions