CODEWITHSUNDEEP
pythonsqlalchemy
Daniel
Daniel

Reputation: 1674

SQLAlchemy: How to filter on a joined table without importing the model

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

Answers (1)

G.S
G.S

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

Related Questions