CODEWITHSUNDEEP
sqlalchemy
jmoney1217
jmoney1217

Reputation: 21

sqlalchemy: How do I do this simple subquery in sqlalchemy?

Two models:

class this(DeclarativeBase):

    __tablename__ = 'this'

    'Columns'
    id = Column(Integer, primary_key=True)

    'Relations'
    that = relation('that', foreign_keys=id, backref='this')

class that(DeclarativeBase):

    __tablename__ = 'that'

    'Columns'
    id = Column(Integer, primary_key=True)
    this_id = Column(Integer, ForeignKey('this.id'))

I want to run this simple SQL Query:

SELECT id, (SELECT COUNT(*) FROM that WHERE this_id = this1.id) AS thatcount FROM this AS this1

I can achieve the same RESULTS in sqlalchemy by doing:

results = session.query(model.this.id, 
                        func.count(model.that.id).label('thatcount')) \
                 .join(model.that) \
                 .group_by(model.this.id)

BUT, the resultant SQL is not what I want:

SELECT
this.id AS this_id,
count(that.id) AS thatcount 
FROM this
INNER JOIN that ON this.id = that.this_id
GROUP BY this.id

I am missing a couple of fundamental ideas in sqlalchemy...

1) How do I "label" tables in FROM clauses? 2) How do I create subqueries that reference results from the parent query?

Hopefully this is something simple that I am just not understanding, as I'm relatively new to sqlalchemy... Of course I can just run raw SQL, but I am impressed by sqlalchemy and I'm sure this is possible.

Any help would be much appreciated!

Upvotes: 2

Views: 3297

Answers (1)

van
van

Reputation: 76962

qry = select([
        this.id,
        select([func.count().label('xx')], this.id == that.this_id).as_scalar().label('thatcount'),
        ])

produces:

SELECT this.id, (SELECT count(*) AS xx
FROM that
WHERE this.id = that.this_id) AS thatcount
FROM this

To answer your questions directly:

  1. use label()
  2. you do not need that, you just use the whereclause of the select to indicate the join condition between the main query and the subquery.

Note that I prefer func.count(that.id) to func.count() though, as it is more explicit.

Upvotes: 3

Related Questions