boolean column_property using exists

[Kevin S]

I've seen slight variations on this topic, but not exactly what I'm looking for.

I want to use a column_property (or something like it) to map the existence of some Child relationship.

Say for example, I have two summary pages in a web app. One for displaying rows of Parent objects, and one for displaying rows of Child objects.

I have mapped Parent to Child as a relationship.

Now, on the Parent summary there will be a link to the Child summary. However, we only want the link if there are indeed Child relationships for that Parent. Since a count is not needed, I would prefer to use an EXISTS clause. Some Parents could have thousands of Child objects, so count would not be the most performant solution.

Here is a contrived example of the Flask-SQLAlchemy models:

class Child(db.Model):

    __tablename__ = "child"

    child_key = db.Column(db.Integer, primary_key=True)

    parent_key = db.Column(db.Integer, db.ForeignKey("Parent.parent_key"))

class Parent(db.Model):

    __tablename__ = "parent"

    parent_key = db.Column(db.Integer, primary_key=True)

    children = db.relationship("Child",

        primaryjoin="Parent.parent_key==Child.parent_key",

        foreign_keys="[Child.parent_key]")

So, how could I implement some kind of property or method that will tell me if Parent.has_children, using an EXISTS query? 

I've seen the examples of using db.select() in a column_property, but I can't get my head around the syntax for doing an exists. I am looking for some approach that would leverage the defined relationship for children, because in practice it could be a quite complex relationship, which I do not wish to define more than once.

Any help is appreciated.

[Michael Bayer]

so you want to embed an exists() into column_property(), and the way to make an exists() is at http://docs.sqlalchemy.org/en/rel_0_9/core/selectable.html?highlight=exists#sqlalchemy.sql.expression.exists.

so,     Parent.has_children = column_property(exists().where(Child.parent_id == Parent.id))

note that I’m adding “has_children” to Parent after we have the Child and Parent classes available already.     To set it up inline you’d use “id” for Parent.id the way it is in the second example at http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#using-column-property.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.