I'm using Postgresql with sqlalchemy ORM
I'm trying to use a sub-query to return an array of classes in a query.
subquery1 = session.query(Coupon).filter(Coupon.date > today)
subquery2 = session.query(Coupon).filter(Coupon.rate > 5)
result = session.query(User,func.array(subquery1.label("active")),
func.array(subquery1.label("expensive")))
this throws an error
(psycopg2.errors.SyntaxError) subquery must return only one column
When I only query the id from the subquery1 and 2 session.query(Coupon.id) it works fine and returns a list of ids that can be accessed with result[1] and result[2] which is exactly what I want except that I need the full class with all its attributes not just the attributes.
The current result is something like
[(<User object at 0x000001E88378F2E0>, [5,3], [])]
and I want it to be like this
[(<User object at 0x000001E88378F2E0>, [<Coupon object at 0x000001E88378F2E0>,<Coupon object at 0x000001E88378F2E0>], [])]
I also like to do it in 1 query instead of 3
The class Coupon and User are joined by a 3rd table that contains user_id and coupon_id but that is not relevant