Re: [Sqlalchemy-users] Subqueries in functions

[Rick Morrision]

No, they're supposed to be subqueries -- correlated subqueries. That maybe got lost in simplifying and pasting the code.

Note that the problem isn't the inclusion of the subqueries in the column clause -- that's correct.
It's the repetition of those subqueries in the FROM clause that's the issue.

I was able to get around the issue this time by splitting the query into two and pre-fetching the subquery results, but the general case of subqueries in functions looks bork.

Rick

On 3/12/06, Michael Bayer <[EMAIL PROTECTED]> wrote:

youre sticking two select() objects in your column clause, inside the functions....so they come out as SELECT statements in your column clause, i.e. subqueries.

what SQL are you looking to produce ?   maybe this ?

q = db.sql.select([ L.c.id, L.c.nm, Z.c.zipcode, db.sql.func.latlondist(Z.c.latitude, Z.c.longitude).label('dist')],

                 db.sql.and_(L.c.zip == Z.c.zipcode, Z.c.zipcode==zip),

                 order_by = ['dist', L.c.nm]

                 )

SELECT loc.id, loc.nm, zipcodes.zipcode, latlondist(zipcodes.latitude, zipcodes.longitude) AS dist

FROM loc, zipcodes

WHERE loc.zip = zipcodes.zipcode AND zipcodes.zipcode = ? ORDER BY dist, loc.nm

On Mar 12, 2006, at 4:51 PM, Rick Morrision wrote:

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