Hey Mike,
Interesting - in my demo environment it appears to work fine. However, in my
production environment it actually reverses the parameters!
The actual property looks like this:
CoreDb.CustomerTour.mapper.add_property('equipment_history',
DB.relation(CheckoutEntry.mapper,
primaryjoin=DB.and_(CoreDb.CustomerTour.c.cust_id==CheckoutEntry.c.cust_id,
CoreDb.CustomerTour.c.start_date <=
CheckoutEntry.c.ts_checkout,
CoreDb.CustomerTour.c.finish_date >=
CheckoutEntry.c.ts_checkout)))
The result from the echo logs is (after being suitably trimmed):
SELECT checkout_trail.ts_returned, checkout_trail.checkout_id,
checkout_trail.state_returned_id,
checkout_trail.ts_checkout, checkout_trail.item_id,
checkout_trail.cust_id
FROM equipment.checkout_trail
WHERE checkout_trail.cust_id = %(lazy_8e42)s
AND checkout_trail.ts_checkout <= %(lazy_e92c)s
AND checkout_trail.ts_checkout >= %(lazy_98bc)s
{'lazy_8e42': 19, 'lazy_98bc': datetime.date(2006, 5, 12), 'lazy_e92c':
datetime.date(2006, 2, 1)}
As you can see, it's looking to be greater then the larger value, and smaller
then the smaller one :P That ain't right!
The actual row, for reference:
test_be_1=# SELECT cust_id, start_date, finish_date
FROM public.customer_tours
WHERE cust_id = 19;
cust_id | start_date | finish_date
---------+------------+-------------
19 | 2006-02-01 | 2006-05-12
(1 row)
Cheers!
-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
-------------------------------------------------------
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
