| you know I am looking thru the docs as well as the unit tests and I am surprised that I dont seem to have ever even completed this functionality, i.e. being able to stick a SELECT inside the column clause of another select. which is very strange since I was pretty sure I hit all these in at least a rudimentary form early on....you can for now possibly try sticking str(qlat) and str(qlng) in there so they are just straight textual strings. that would explain why this doesnt work, i.e. if it did work early on , no unit test was created to insure that it kept working through all the changes ive been making.
I was hoping to try to release 0.1.4 tonight, as theres a huge number of changes piling up in SVN. sorry this particular fix might not make it in time ! On Mar 12, 2006, at 8:34 PM, Rick Morrision wrote: 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
|
