Hey Mike, Brilliant as always!
Care to hazard a guess as to what's with the sketchily required foreignkey field? -G On Monday, April 24, 2006, 9:24:58 PM, you wrote: > gambit - > the SQL clause it generates for the lazy criterion has the column > "timeval" in it twice, which resulted in a name collision for the > bind parameter names that gets munged upon compilation (i.e. > data_timeval, data_timeval_1), so that the lazy loader was not > setting the parametrs properly. it now uses anonymous names for > those parameter names which it keeps track of internally. plus your > test case is added as a unit test. see http://www.sqlalchemy.org/ > trac/changeset/1332 for all the details. > On Apr 24, 2006, at 1:44 PM, Gambit wrote: >> Hey All - >> >> Putting together my latest testcase for multi-table queries I >> stumbled across >> this problem. Given tables Info, Data, and the relation table >> Rels, I'm trying to find the records in 'Data' for which a >> 'timeval' falls >> between a 'start' and 'finish' value in Rels, all of which is tied >> together by >> a foreign key to info.pk. Sample code and output is, of course, >> attached for >> those who wish to jump right to the chase. >> >> The query itself looks like this: >> >> SELECT infos.pk, data.data_val >> FROM infos JOIN rels ON infos.pk = rels.info_pk >> JOIN data ON infos.pk = data.info_pk AND data.timeval >> >= rels.start AND data.timeval <= rels.finish; >> >> This translates out to the following property in the mapper: >> Relation.mapper.add_property('datas', relation(Data.mapper, >> primaryjoin=and_(Relation.c.info_pk==Data.c.info_pk, >> Data.c.timeval >= Relation.c.start, >> Data.c.timeval <= Relation.c.finish), >> foreignkey=Data.c.info_pk)) >> >> As a side note, on my test case the 'foreignkey=' parameter was >> required, but >> in my actual production environment, it isn't, though the queries >> and schema >> are as identical as I can make them otherwise. Any ideas for that? >> >> Viewing the contents of the property via poking at each level to >> cause it to >> load: >> >> info = Information.mapper.get(1) >> assert info >> assert len(info.rels) == 2 >> assert len(info.rels[0].datas) == 3 >> >> Yields an interesting query (trimmed to be succinct but otherwise >> identical to >> what SA spits out): >> >> SELECT data.data_pk, data.data_val, data.timeval, data.info_pk >> FROM data >> WHERE data.info_pk = ? AND data.timeval >= ? AND data.timeval <= ? >> [1, 19, None] >> >> The three parameters are the 'info_pk' foreign key, the timeval to >> be greater >> then (the 'start' value), and the timeval to be within ('finish'). >> >> The quick amongst you will spot that 'None' is not a valid value >> for the >> second timevalue! In fact, the first time value /is/ the second >> 'timeval', as >> the correct values should be '10', and the second '19'. So the end >> result is >> the query parameters are... misaligned? >> >> Comments? Suggestions? >> -G >> <many_to_many_range_select.log> >> <many_to_many_range_select.py> ------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
