[Sqlalchemy-users] Subqueries in functions

[Rick Morrision]

SA seems intent on adding subqueries used in DB functions to the FROM list of a query:

        Z = S.Table('zipcodes', dbeng,
                                S.Column('zipcode',  S.VARCHAR(5), nullable=False, primary_key=True),
                                S.Column('city',     S.VARCHAR(30), nullable=False),
                                S.Column('state',    S.CHAR(2), nullable=False),
                                S.Column('latitude', S.FLOAT, nullable=False),
                                S.Column('longitude',S.FLOAT, nullable=False),
                                S.Column('areacode', S.CHAR(3))
                                )
       L = S.Table('loc', dbeng,
                          S.Column('id',     S.INT, S.Sequence('loc_idloc_seq'), primary_key=True),
                          S.Column('typ',    S.VARCHAR(6), nullable=False),
                          S.Column('nm',     S.VARCHAR(50)),
                          S.Column('street', S.VARCHAR(255))
                          S.Column('city',   S.VARCHAR(50)),
                          S.Column('st',     S.CHAR(2))
                          S.Column('zip',    S.VARCHAR(16))
                           )

        zip = '12345'
        qlat = db.sql.select([Z.c.latitude], Z.c.zipcode == zip)
        qlng = db.sql.select([Z.c.longitude], Z.c.zipcode == zip)

        # get closest locations to "zip"      
        q = db.sql.select([L.c.id, L.c.nm, L.c.abbrev, Z.c.zipcode, db.sql.func.latlondist(qlat, qlng).label('dist')],
                           L.c.zip == Z.c.zipcode,
                           order_by = ['dist', L.c.nm]
                           )

gives:
----------------------------
SELECT loc.id, loc.nm, loc.abbrev, zipcodes.zipcode, latlondist((SELECT zipcodes.latitude AS latitude
FROM zipcodes
WHERE zipcodes.zipcode = %(zipcodes_zipcode)s), (SELECT zipcodes.longitude AS longitude
FROM zipcodes
WHERE zipcodes.zipcode = %(zipcodes_zipcode_1)s)) AS dist
FROM loc, zipcodes, (SELECT zipcodes.latitude AS latitude
FROM zipcodes
WHERE zipcodes.zipcode = %(zipcodes_zipcode)s), (SELECT zipcodes.longitude AS longitude
FROM zipcodes
WHERE zipcodes.zipcode = %(zipcodes_zipcode_1)s)
WHERE loc.zip = zipcodes.zipcode AND loc.typ = %(loc_typ)s ORDER BY dist, loc.nm


How do I get rid of the subqueries in the FROM list?

Thx,
Rick