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
