SQLAlchemy mapping table columns with filters
<p>I have a table in PostgreSQL that includes information about documents. Lets say something like that:</p>

<pre><code>table: doc

id (int)
name (string)
type (int)
</code></pre>

<p><strong>type</strong> is a category for a document (e.g. 1 - passport, 2 - insurance etc.). Also I have different tables with additional information for every docyment type.</p>

<pre><code>table: info

id (int)
doc_id (fk)
info (additional columns)
</code></pre>

<p>I want to have a SQLAlchemy model to work with each type of document linked with it's additional information and be able to manage columns to display (for Flask-Admin if it is important).</p>

<p>Now to join two tables into some sort of "model" I used <a href="http://sqlalchemy.readthedocs.io/en/latest/orm/mapping_columns.html" rel="nofollow" title="Mapping Table Columns">Mapping Table Columns</a> from the SQLAlchemy documentation like that (when there was only one type of documents):</p>

<pre><code>class DocMapping(db.Model):

    __table__ = doc.__table__.join(info)
    __mapper_args__ = {
        'primary_key': [doc.__table__.c.id]
    }
</code></pre>

<p>Now the question is that: <strong>how to create multiple classes inherited from db.Model (DocPassportMapping, DocInsuranceMapping etc.) based on doc.type column?</strong></p>

<p>Something like that:</p>

<pre><code>__table__ = doc.__table__.join(info).filter(doc.type)
</code></pre>

<p>That is obviously not working because we don't have a <strong>query</strong> object here.</p>

<p>If I understood you correctly, you wish to have an <a href="http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#mapping-class-inheritance-hierarchies" rel="nofollow">inheritance hierarchy</a> based on <code>DocMapping</code> with <code>DocMapping.type</code> as the polymorphic identity. Since you have not provided a complete example, here is a somewhat similar structure. It has differences for sure, but should be applicable to yours. This uses <a href="http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#single-table-inheritance" rel="nofollow">single table inheritance</a> on top of the joined mapping.</p>

<p>The models:</p>

<pre><code>In [2]: class Doc(Base):
   ...:     id = Column(Integer, primary_key=True, autoincrement=True)
   ...:     name = Column(Unicode)
   ...:     type = Column(Integer, nullable=False)
   ...:     __tablename__ = 'doc'
   ...:     

In [3]: class Info(Base):
   ...:     __tablename__ = 'info'
   ...:     doc_id = Column(Integer, ForeignKey('doc.id'), primary_key=True)
   ...:     value = Column(Unicode)
   ...:     doc = relationship('Doc', backref=backref('info', uselist=False))
   ...:     

In [4]: class DocMapping(Base):
   ...:     __table__ = Doc.__table__.join(Info)
   ...:     __mapper_args__ = {
   ...:         'primary_key': (Doc.id, ),
   ...:         # These declare this mapping polymorphic
   ...:         'polymorphic_on': Doc.type,
   ...:         'polymorphic_identity': 0,
   ...:     }
   ...:     

In [5]: class Passport(DocMapping):
   ...:     __mapper_args__ = {
   ...:         'polymorphic_identity': 1,
   ...:     }
   ...:     

In [6]: class Insurance(DocMapping):
   ...:     __mapper_args__ = {
   ...:         'polymorphic_identity': 2,
   ...:     }
   ...:     
</code></pre>

<p>Testing:</p>

<pre><code>In [7]: session.add(Insurance(name='Huono vakuutus',
   ...:                       value='0-vakuutus, mitÃ¤Ã¤n ei kata'))

In [8]: session.commit()

In [15]: session.query(DocMapping).all()
Out[15]: [&lt;__main__.Insurance at 0x7fdc0a086400&gt;]

In [16]: _[0].name, _[0].value
Out[16]: ('Huono vakuutus', '0-vakuutus, mitÃ¤Ã¤n ei kata')
</code></pre>

<p>The thing is: you probably do not want multiple classes that inherit from <code>db.Model</code> as base, but classes that inherit from <code>DocMapping</code>. It makes a lot more sense as a hierarchy.</p>

python postgresql flask sqlalchemy flask-admin