SQLAlchemy subquery in from clause without join
I presume this is PostgreSQL.
To create a subquery, use subquery()
method. The resulting object can be used as if it were Table
object. Here's how your query would look like in SQLAlchemy:
subq1 = session.query(
func.array_to_string(Company.groups, ',').label('group_name')
).filter(
(Company.status == 'active') &
(func.array_to_string(Company.groups, ',').like(term))
).limit(limit).subquery()
subq2 = session.query(
func.regexp_split_to_table(subq1.c.group_name, ',')
.distinct()
.label('group')
).subquery()
q = session.query(subq2.c.group).\
filter(subq2.c.group.like(term)).\
order_by(subq2.c.group).\
limit(limit)
However, you could avoid one subquery by using unnest
function instead of converting array to string with arrayt_to_string
and then splitting it with regexp_split_to_table
:
subq = session.query(
func.unnest(Company.groups).label('group')
).filter(
(Company.status == 'active') &
(func.array_to_string(Company.groups, ',').like(term))
).limit(limit).subquery()
q = session.query(subq.c.group.distinct()).\
filter(subq.c.group.like(term)).\
order_by(subq.c.group).\
limit(limit)
Related videos on Youtube
free2use
Updated on July 09, 2022Comments
-
free2use almost 2 years
i need a little help. I have following query and i'm, curious about how to represent it in terms of sqlalchemy.orm. Currently i'm executing it by session.execute. Its not critical for me, but i'm just curious. The thing that i'm actually don't know is how to put subquery in FROM clause (nested view) without doing any join.
select g_o.group_ from ( select distinct regexp_split_to_table(g.group_name, E',') group_ from ( select array_to_string(groups, ',') group_name from company where status='active' and array_to_string(groups, ',') like :term limit :limit ) g ) g_o where g_o.group_ like :term order by 1 limit :limit
I need this subquery thing because of speed issue - without limit in the most inner query function regexp_split_to_table starts to parse all data and does limit only after that. But my table is huge and i cannot afford that.
If something is not very clear, please, ask, i'll do my best)
-
bsplosion over 5 yearsOne important piece that wasn't explicitly mentioned (but could be inferred from the samples): you need to explicitly use
subq1.c
instead ofsubq1
to use the query as a subquery table, else the query won't compile due to missing attributes. See this doc for details.