| 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
|
