SqlAlchemy Query to Select Rows Without Children

Question

I have a Tag table which can have a parent Tag of the same class.

I want a query to return all Tag instances without any children.

Here is the code for the SqlAlchemy class:

class Tag(db.Model):
    __tablename__ = 'tags'

    id = db.Column(db.String(32), primary_key=True)
    name = db.Column(db.String(45),nullable=False)
    subject_id = db.Column(db.Integer, db.ForeignKey('subjects.id'), nullable=False)
    parent_tag_id = db.Column(db.Integer, db.ForeignKey('tags.id'), nullable=True)

    subject = db.relationship('Subject', backref=db.backref('tags', lazy='dynamic'))
    parent_tag = db.relationship('Tag',
                                 remote_side=[id],
                                 backref=db.backref('children', lazy='dynamic'))

    def __init__(self, name, subject_id, parent_tag_id=None):
        self.id = uuid.uuid4().hex
        self.name = name
        self.subject_id = subject_id
        self.parent_tag_id = parent_tag_id

And here is my best attempt at the query:

def get_all_subject_tags_ordered():
    _child_tag = aliased(Tag)
    return db.session.query(Tag)\
        .join(_child_tag, Tag.children)\
        .filter(func.count(Tag.children) == 0)\
        .filter(Tag.subject_id.isnot(None))\
        .order_by(Tag.name)\
        .all()

This is giving me the error:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1111, u'Invalid use of group function') [SQL: u'SELECT tags.id AS tags_id, tags.name AS tags_name, tags.subject_id AS tags_subject_id, tags.parent_tag_id AS tags_parent_tag_id \nFROM tags INNER JOIN tags AS tags_1 ON tags.id = tags_1.parent_tag_id \nWHERE count(tags.id = tags.parent_tag_id) = % (count_1)s AND tags.subject_id IS NOT NULL ORDER BY tags.name'] [parameters: {u'count_1': 0}]

Thank you very much for your help.

Answer 1

In general: db.session.query(Parent).filter(Parent.children==None) will find all Parents with no children.

So try:

return db.session.query(Tag)\
    .filter(Tag.children == None, Tag.subject_id != None)\
    .order_by(Tag.name)\
    .all()