[sqlalchemy] SQLAlchemy - Problem with an association table and dates in primary join
Hi,
I am working on defining my mapping with SQLAlchemy and I am pretty
much done except one thing. I have a 'resource' object and an
association table 'relation' with several properties and a
relationship between 2 resources. What I have been trying to do almost
successfully so far, is to provide on the resource object 2
properties: parent and children to traverse the tree stored by the
association table. A relation between 2 properties only last for a
while, so there is a start and end date. Only one resource can be the
parent of another resource at a time.
My problem is that if I expire one relation and create a new one, the
parent property is not refreshed. I am thinking maybe there an issue
with the primaryjoin for the parent property of resource.
Here is some code:
resource_table = model.tables['resource']
relation_table = model.tables['resource_relation']
mapper(Resource, resource_table,properties = {'type' :
relation(ResourceType,lazy = False), 'groups' : relation(Group,
secondary = model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date func.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
mapper(Relation, relation_table, properties = {'resource' :
relation(Resource, primaryjoin = (relation_table.c.res_id ==
resource_table.c.res_id)), 'parent' : relation(Resource, primaryjoin =
(relation_table.c.parent_id == resource_table.c.res_id))})
oldrelation = resource.parent
oldrelation.end_date = datetime.today()
relation = self.createRelation(parent, resource)
# Here the relation object has not replaced oldrelation in the
resource object
Hi,
I am working on defining my mapping with SQLAlchemy and I am pretty
much done except one thing. I have a 'resource' object and an
association table 'relation' with several properties and a
relationship between 2 resources. What I have been trying to do almost
successfully so far, is to provide on the resource object 2
properties: parent and children to traverse the tree stored by the
association table. A relation between 2 properties only last for a
while, so there is a start and end date. Only one resource can be the
parent of another resource at a time.
My problem is that if I expire one relation and create a new one, the
parent property is not refreshed. I am thinking maybe there an issue
with the primaryjoin for the parent property of resource.
Here is some code:
resource_table = model.tables['resource']
relation_table = model.tables['resource_relation']
mapper(Resource, resource_table,properties = {'type' :
relation(ResourceType,lazy = False), 'groups' : relation(Group,
secondary = model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date func.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
mapper(Relation, relation_table, properties = {'resource' :
relation(Resource, primaryjoin = (relation_table.c.res_id ==
resource_table.c.res_id)), 'parent' : relation(Resource, primaryjoin =
(relation_table.c.parent_id == resource_table.c.res_id))})
oldrelation = resource.parent
oldrelation.end_date = datetime.today()
relation = self.createRelation(parent, resource)
# Here the relation object has not replaced oldrelation in the
resource object
Any idea ?
Thanks,
Richard Lopes
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Can we use dates to define a relation with the mapper ?
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
Any help is welcome.
Thanks,
Richard Lopes
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Can we use dates to define a relation with the mapper ?
Hi,
Thanks for the help but I think I got it working.
Look here:
http://stackoverflow.com/questions/2384438/sqlalchemy-can-we-use-date-comparison-in-relation-definition
Cheers,
Richard
2010/3/5 Michael Trier mtr...@gmail.com
Hello,
On Mar 4, 2010, at 10:50 PM, Richard Lopes wrote:
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
I imagine you're getting bitten because your datetime.now() is getting
evaluated at compile time. You might need to make it a callable. That said
I'm unsure about whether or not a callable will work with SQLAlchemy. I
might be able to write a test case tomorrow.
Michael
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
R. LOPES
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
