Hey All,
Outpacing myself a bit: if I reverse the query in the sample program to a
similar format:
Relation.mapper.add_property('datas', relation(Data.mapper,
primaryjoin=and_(Relation.c.info_pk==Data.c.info_pk,
Relation.c.start <= Data.c.timeval,
Relation.c.finish >= Data.c.timeval),
foreignkey=Data.c.info_pk))
The assertions throw. Something to add to the test case as well, unless I'm
doing something horribly wrong?
-G
On Monday, April 24, 2006, 11:29:21 PM, you wrote:
> 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
-------------------------------------------------------
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
