SQLAlchemy - Problem with an association table and dates in primary join

Question

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

Answer 1

Hi,

I actually have found the reason of my trouble and got it working. See here

Cheers,

Richard Lopes

Answer 2

Consider using >= instead of > in date comparison.