SQLAlchemy subquery in from clause without join

Question

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)

Audrius Kažukauskas · Accepted Answer · 2013-08-16 09:38:26Z

23

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)

edited Aug 16, 2013 at 9:38

answered Aug 15, 2013 at 21:28

Audrius Kažukauskas

13.6k4 gold badges56 silver badges54 bronze badges

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

2 Comments

bsplosion Over a year ago

One important piece that wasn't explicitly mentioned (but could be inferred from the samples): you need to explicitly use subq1.c instead of subq1 to use the query as a subquery table, else the query won't compile due to missing attributes. See this doc for details.

robertspierre Aug 17 at 11:54

Notice that Session.query is legacy in SQLAlchemy 2.0

Collectives™ on Stack Overflow

SQLAlchemy subquery in from clause without join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related