title: SQLAlchemy: find child objects in a relationship with NULL parent
body: <p>I have a class which has a parent-child relationship:</p>
  
  <pre><code>Base = declarative_base()
  
  class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent")
    def all_children(self):
      pass # I want self.children + "Child where parent_id = NULL"
  
  class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent_table.id')
  </code></pre>
  
  <p>I would like to add a function to my parent which returns the parent's children in the relationship, and also all Child objects which have their parent_id column set to NULL.</p>
  
  <p>The whole situation is a bit more complex because the classes are actually a case of joined table inheritance like this: <a href="http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/generic_associations/table_per_related.html" rel="nofollow">table_per_related</a>, but I don't even know where to start so would probably be able to figure it out from there.</p>
  
  <p>(The whole thing must be usable in the context of a web service via Flask)</p>
  
  <p><strong>Edit:</strong> Update. This is a minimal implementation of what I really wanted to do, as I can't quite manage to translate the first answer into something that works in this context:</p>
  
  <pre><code>from sqlalchemy.ext.declarative import declarative_base, declared_attr
  from sqlalchemy import Column, Integer, String, ForeignKey
  from sqlalchemy.orm import relationship
  
  from sqlalchemy.orm import Session
  from sqlalchemy import create_engine
  
  class BaseCols:
    id = Column(Integer, primary_key=True)
    name = Column(String)
  
    def __repr__(self):
      return "&lt;{}: {} - {}&gt;".format(self.__class__.__name__, self.id, self.name)
  
    @declared_attr
    def __tablename__(cls):
      return cls.__name__.lower()
  
  Base = declarative_base(cls=BaseCols)
  
  class Child(BaseCols):
    pass
  
  class HasChild:
    @declared_attr
    def children(cls):
      cls.Child = type("{}Child".format(cls.__name__),
                       (Child, Base,),
                       dict(
                            __tablename__="{}_children".format(cls.__tablename__),
                            parent_id=Column(Integer, ForeignKey("{}.id".format(cls.__tablename__))),
                            parent=relationship(cls)
                       )
                      )
      return relationship(cls.Child)
  
    def all_children(self):
      pass
  
  class Foo(Base, HasChild):
    __tablename__ = 'foo'
    __mapper_args__ = {'concrete': True}
  
  class Bar(Base, HasChild):
    __tablename__ = 'bar'
    __mapper_args__ = {'concrete': True}
  
  
  if __name__ == "__main__":
    engine = create_engine('sqlite://', echo=True)
    Base.metadata.create_all(engine)
  
    session = Session(engine)
  
    session.add_all([
      Foo(
        name = "Foo the first!",
        children = [
          Foo.Child(name="Heir Apparent."),
          Foo.Child(name="Spare.")
        ]
      ),
      Foo(
        name = "Foo the second...",
        children = [
          Foo.Child(name="Some child."),
        ]
      ),
      Bar(
        name = "Bar the first!",
        children = [
          Bar.Child(name="Bar's.")
        ]
      ),
      Foo.Child(name="whoops"),
    ])
  
    session.commit()
  
    foo1 = session.query(Foo).first()
    print(foo1)
    print(foo1.children)
    print(foo1.all_children(session))
  </code></pre>