Saturday, 24 July 2021

SQLAlchemy multiple backrefs causing problems

I'm using SQLAlchemy with Python (linking to an MySQL database) and am having a little design issue, which has presented itself as a problem with a backref.

So the situation is this. I have a SearchGroup which contains TargetObjects and SearchObjects. These are both many to many relationships, and so the SearchGroup table comes with two association tables, one for each. The SearchObject is the same time for any SearchGroup, but the TargetObject varies. So far so good. The whole idea here is that a SearchObject is simply a string with a few other variables, and a SearchGroup compares them all to a given string and then, if there's a match, supplies the target objects.

Now for some code: the declaration of these three classes, although with the parent logic hidden for brevity:

class AssocTable_GroupCMClassesGrades_Grades(AssociationTable_Group_TargetObjectsParent, med.DeclarativeBase):
    __tablename__ = 'AssocTable_GroupCMClassesGrades_Grades'
    _groupsTableName = 'SearchGroup_CMClasses_Grades'
    _targetObjectsTableName = 'Grades'

class AssocTable_GroupCMClassesGrades_SearchObjects(AssociationTable_Group_SearchObjectsParent, med.DeclarativeBase):
    __tablename__ = 'AssocTable_GroupCMClassesGrades_SearchObjects'
    _groupsTableName = 'SearchGroup_CMClasses_Grades'
    _searchObjectsTableName = 'SearchObjects'

class SearchGroup_CMClasses_Grades(SearchObjectGroupParent, med.DeclarativeBase):
    __tablename__ = 'SearchGroup_CMClasses_Grades'
    targetAssociatedTargetObjectTableName = 'AssocTable_GroupCMClassesGrades_Grades'
    targetAssociatedSearchObjectTableName = 'AssocTable_GroupCMClassesGrades_SearchObjects'
    targetClassName = 'Grade'
    myClassName = 'SearchGroup_CMClasses_Grades'
    searchObjectClassName = 'SearchObject'
    searchObjectChildrenBackRefName = 'Groups'

The top two are the association tables and the bottom is the main class. The strings are used to set up various foreign keys and relationships and such.

Let's look at a specific example, which is crucial to the question:

@declared_attr
def searchObject_childen(cls):
    return relationship(f'{cls.searchObjectClassName}', secondary=f'{cls.targetAssociatedSearchObjectTableName}', backref=f'{cls.searchObjectChildrenBackRefName}')

This is inside the SearchObjectGroupParent class and, as you can see, is for the 'children' of the SearchGroup, which are SearchObjects.

So now to the problem.

That all works rather well, except for one thing. If I could direct your attention back to the large bit of code above, and to this line:

   searchObjectChildrenBackRefName = 'Groups'

This, as seen in the second posted piece of code (the declared_attr one), sets up a backref; a property in the target - it creates that property and then populates it. I'm not an expert at this by any means so I won't pretend to be. The point is this: if I create another SearchObjectGroupParent derived class, like the one above, with its association tables, I can't put another 'Groups' property into SearchObject - in fact it will throw an error telling me as much:

sqlalchemy.exc.ArgumentError: Error creating backref 'Groups' on relationship 'SearchGroup_CMClasses_Grades.searchObject_childen': property of that name exists on mapper 'mapped class SearchObject->SearchObjects'

There is a rather unsatisfying way to solve this, which is to simple change that name each time, but then the SearchObject won't have a common list of SearchGroups. In fact it will contain the 'Groups' property for every SearchGroup. This will work, but will be messy and I'd rather not do it. What I would like is to say 'okay, if this backref already exists, just use that one'. I don't know if that's possible, but I think such a thing would solve my problem.

Edit: I thought an image might help explain better:

Figure 1: what I have now:

enter image description here

The more of these objects derived from SearchObjectsGroupParent I have, the messier it will be (SearchObject will contain Groups03, Groups04, Groups05, etc.).

Figure 2: what I want:

enter image description here



from SQLAlchemy multiple backrefs causing problems

No comments:

Post a Comment