Hey Mike,
Yeah, autoload=True. I've attached the schemas for the relevant tables below,
to compare against the ones in the test case. Still running 1333 (haven't
svnuped yet) my application code actually crashes when I try to access the
variable. I can't get the testcase to reproduce, which is extremely
frustrating :( Annotated and commented output is included as well.
Cheers,
-G
On Monday, April 24, 2006, 11:55:49 PM, you wrote:
> youve got the identical code on both sides, right ? the "foreignkey"
> thing shouldnt be needed, it implies something is not correct
> elsewhere. are you using tables with autoload=True ?
> On Apr 24, 2006, at 5:29 PM, Gambit 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
>>> [email protected]
>>> 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
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users[2006-04-25 12:34:48,226] [engine]: SELECT checkout_trail.ts_returned AS
checkout_trail_ts_returned,
checkout_trail.checkout_id AS
checkout_trail_checkout_id,
checkout_trail.state_returned_id AS
checkout_trail_state_ret_5a35,
checkout_trail.ts_checkout AS
checkout_trail_ts_checkout,
checkout_trail.item_id AS
checkout_trail_item_id,
checkout_trail.cust_id AS checkout_trail_cust_id
FROM equipment.checkout_trail
WHERE checkout_trail.cust_id = %(lazy_d26)s AND
checkout_trail.ts_checkout >= %(lazy_e6a5)s AND
checkout_trail.ts_checkout <= %(lazy_aee)s
[2006-04-25 12:34:48,226] [engine]: {'lazy_aee': datetime.date(2006, 5, 12),
'lazy_d26': 19, 'lazy_e6a5': datetime.date(2006, 2, 1)}
# As you can see, the parameters are correct in 1333 because I reversed the
order
# of the comparisons back around. Haven't tested in 1334 yet.
Traceback (most recent call last):
...
<snip>
...
File "Common.py", line 32, in __init__
for i in tour.equipment_history:
Source around this line:
tour = cust.TourAt()
for i in tour.equipment_history:
>>>Last Line>>> if i.ts_returned:
Maps in question:
'cust' is my Customer class
'TourAt' returns a 'CustomerTour' object with a particular date range, in
this case the tour for "now"
'equipment_history' is a result of the following problematic mapper as a set
of 'CheckoutEntry' classes:
CustomerTour.mapper.add_property('equipment_history',
relation(CheckoutEntry.mapper,
primaryjoin=and_(CustomerTour.c.cust_id==CheckoutEntry.c.cust_id,
CheckoutEntry.c.ts_checkout >=
CustomerTour.c.start_date,
CheckoutEntry.c.ts_checkout <=
CustomerTour.c.finish_date),
>>> foreignkey=CheckoutEntry.c.cust_id))
|
--- I tried this with and without the foreignkey line, no difference.
Rest of the traceback, all files listed here are relative to
SQLAlchemy-0.1.6.1333-py2.4.egg:
File "sqlalchemy\attributes.py", line 62, in __get__
return self.manager.get_list_attribute(obj, self.key)
File "sqlalchemy\attributes.py", line 346, in get_list_attribute
return self.get_history(obj, key, **kwargs)
File "sqlalchemy\attributes.py", line 415, in get_history
return self.get_unexec_history(obj, key).history(**kwargs)
File "sqlalchemy\attributes.py", line 257, in history
value = self.callable_()
File "sqlalchemy\mapping\properties.py", line 621, in lazyload
result = self.mapper.using(session).select_whereclause(self.lazywhere,
order_by=order_by, params=params)
File "sqlalchemy\mapping\query.py", line 137, in select_whereclause
return self._select_statement(statement, params=params)
File "sqlalchemy\mapping\query.py", line 213, in _select_statement
return self.instances(statement.execute(**params), **kwargs)
File "sqlalchemy\mapping\query.py", line 168, in instances
return self.mapper.instances(session=self.session, *args, **kwargs)
File "sqlalchemy\mapping\mapper.py", line 392, in instances
self._instance(session, row, imap, result,
populate_existing=populate_existing)
File "sqlalchemy\mapping\mapper.py", line 729, in _instance
instance = self._create_instance(session)
File "sqlalchemy\mapping\mapper.py", line 749, in _create_instance
return self.class_(_mapper_nohistory=True,
_sa_entity_name=self.entity_name, _sa_session=session)
File "sqlalchemy\mapping\mapper.py", line 353, in init
oldinit(self, *args, **kwargs)
TypeError: __init__() takes at least 3 arguments (1 given) Table "public.customer_tours"
Column | Type | Modifiers
---------------+-----------------------+-----------
cust_id | integer | not null
tour_operator | integer |
hotel_id | integer |
start_date | date | not null
finish_date | date | not null
tank_type | integer | default 1
group_name | character varying(25) |
notes | text |
package_id | integer |
Indexes:
"cust_tour_pk" PRIMARY KEY, btree (cust_id, start_date, finish_date)
Foreign-key constraints:
"customer_tours_cust_id_fkey" FOREIGN KEY (cust_id) REFERENCES
customers(cust_id) ON DELETE CASCADE
"customer_tours_hotel_id_fkey" FOREIGN KEY (hotel_id) REFERENCES
hotels(hotel_id) ON DELETE RESTRICT
"customer_tours_tank_type_fkey" FOREIGN KEY (tank_type) REFERENCES
tank_types(tank_type_id) ON DELETE RESTRICT
"customer_tours_tour_operator_fkey" FOREIGN KEY (tour_operator) REFERENCES
tours(tour_id) ON DELETE RESTRICT Table "equipment.checkout_trail"
Column | Type | Modifiers
-------------------+--------------------------+--------------------------------------------------------------------------------
cust_id | integer | not null
item_id | integer | not null
ts_checkout | timestamp with time zone | not null
state_returned_id | integer |
ts_returned | timestamp with time zone |
checkout_id | integer | not null default
nextval('equipment.checkout_trail_checkout_id_seq'::regclass)
Indexes:
"checkout_trail_pkey" PRIMARY KEY, btree (checkout_id)
Foreign-key constraints:
"checkout_trail_cust_id_fkey" FOREIGN KEY (cust_id) REFERENCES
customers(cust_id) ON DELETE RESTRICT
"checkout_trail_item_id_fkey" FOREIGN KEY (item_id) REFERENCES
equipment.items(item_id) ON DELETE RESTRICT
"checkout_trail_state_returned_id_fkey" FOREIGN KEY (state_returned_id)
REFERENCES equipment.states(state_id) ON DELETE RESTRICT
