Reputation: 1674
Say I have two models, ModelA and ModelB.
They're connected via a 1:n relationship, e.g. ModelA can have many ModelB associated to it. A normal relationship()
is defined within SQLalchemy and this does work well in general. The problem occurs when I need to do some more complex queries. Lets say, I want to return all ModelB entries, which associated ModelA entry has a particular attribute. What I did so far is:
return db.query(ModelB)\
.filter(
ModelB.user_id == user_id,
ModelB.accepted == True
)\
.join(ModelB.modela_relationship)\
.filter(
ModelB.modela_relationship.attribute1 == some_value,
ModelB.modela_relationship.date > datetime.now()
)\
.all()
I explicitly want to use this form of relationship joining and not defining an aliased
model as I'd need to import the other model for this to work. The reason I don't want to import the model is that this leads to circular imports, as ModelA would require ModelB for a query and ModelB would require ModelA.
This code above yields an error though, which reads:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with ModelB.modela_relationship has an attribute 'attribute1'
How would I do this query (get all results filtered by a joined value) without the need to import the model in SQLalchemy?
Upvotes: 0
Views: 932
Reputation: 565
Leading on from my comment:
If you're desperate to have the function as a method on the schema, something a bit more complex that you could do is import both ModelA and ModelB into a new file and then inherit from the model into a new definition like this:
from .filea import ModelA
from .fileb import ModelB
from . import db
class ModelBExtra(ModelB):
__mapper_args__ = {
"polymorphic_identity": "model_b_extra"
}
def myfunc(self):
return db.query(ModelB).join(
ModelB.modela_relationship
).filter(
ModelB.user_id == user_id,
ModelB.accepted == True,
ModelA.attribute1 == some_value,
ModelA.date > datetime.now()
).all()
which gives you the method on what is essentially the same table, and you just import that into where you need it instead. https://docs.sqlalchemy.org/en/14/orm/inheritance.html
Note that this uses the same underlying table within the SQL database, 'model_b'. It's just declared to SQLAlchemy under a different name because SQLAlchemy can't have schema definitions with the same name. I like to use this occasionally to compartmentalise code and methods to only places where they're needed and maintain that import chain in only one direction as I mentioned in the comment.
I know this isn't really an answer to the exact question, but I can't post code snippets in comments.
Upvotes: 1