title: Discovering referers to SQLAlchemy object
body: <p>I have a lot of model classes with ralations between them with a CRUD interface to edit. The problem is that some objects can't be deleted since there are other objects refering to them. Sometimes I can setup ON DELETE rule to handle this case, but in most cases I don't want automatic deletion of related objects till they are unbound manually. Anyway, I'd like to present editor a list of objects refering to currently viewed one and highlight those that prevent its deletion due to FOREIGN KEY constraint. Is there a ready solution to automatically discover referers?</p>
  
  <p><strong>Update</strong></p>
  
  <p>The task seems to be quite common (e.g. django ORM shows all dependencies), so I wonder that there is no solution to it yet.</p>
  
  <p>There are two directions suggested:</p>
  
  <ol>
  <li>Enumerate all relations of current object and go through their <code>backref</code>. But there is no guarantee that all relations have <code>backref</code> defined. Moreover, there are some cases when <code>backref</code> is meaningless. Although I can define it everywhere I don't like doing this way and it's not reliable.</li>
  <li>(Suggested by van and stephan) Check all tables of <code>MetaData</code> object and collect dependencies from their <code>foreign_keys</code> property (the code of <a href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay" rel="nofollow noreferrer">sqlalchemy_schemadisplay</a> can be used as example, thanks to stephan's comments). This will allow to catch all dependencies between <em>tables</em>, but what I need is dependencies between <em>model classes</em>. Some foreign keys are defined in intermediate tables and have no models corresponding to them (used as <code>secondary</code> in relations). Sure, I can go farther and find related model (have to find a way to do it yet), but it looks too complicated.</li>
  </ol>
  
  <p><strong>Solution</strong></p>
  
  <p>Below is a method of base model class (designed for declarative extention) that I use as solution. It is not perfect and doesn't meet all my requirements, but it works for current state of my project. The result is collected as dictionary of dictionaries, so I can show them groupped by objects and their properties. I havn't decided yet whether it's good idea, since the list of referers sometimes is huge and I'm forced to limit it to some reasonable number.</p>
  
  <pre><code>def _get_referers(self):
      db = object_session(self)
      cls, ident = identity_key(instance=self)
      medatada = cls.__table__.metadata
      result = {}
      # _mapped_models is my extension. It is collected by metaclass, so I didn't
      # look for other ways to find all model classes.
      for other_class in medatada._mapped_models:
          queries = {}
          for prop in class_mapper(other_class).iterate_properties:
              if not (isinstance(prop, PropertyLoader) and \
                      issubclass(cls, prop.mapper.class_)):
                  continue
              query = db.query(prop.parent)
              comp = prop.comparator
              if prop.uselist:
                  query = query.filter(comp.contains(self))
              else:
                  query = query.filter(comp==self)
              count = query.count()
              if count:
                  queries[prop] = (count, query)
          if queries:
              result[other_class] = queries
      return result
  </code></pre>
  
  <p>Thanks to all who helped me, especially stephan and van.</p>