SqlAlchemy Select Relation Type

Question

I have a simple One-to-Many relation mapped with SqlAlchemy:

Base = declarative_base()

class Type(Base):

    __tablename__ = "entity_types"

    type = Column(String(100), primary_key=True)
    description = Column(String(300))

class Entity(Base):

    __tablename__ = "entities"

    id = Column(Integer, primary_key=True)
    type_id = Column('type', String(100), ForeignKey(Types.type), 
                     nullable=False)
    type = relation(Type, backref='entities')
    value = Column(Text, nullable=False)

I want to query all types ever used in an entity. In pure SQL I would accomplish this by:

SELECT entity_types.*
FROM entities
    JOIN entity_types ON entities.type == entity_types.type
GROUP BY entity_types.type

How do I solve this using SqlAlchemy's ORM-Engine?

I've tried these queries, but they all don't return what I want:

session.query(Action.type).group_by(Action.type).all()

session.query(Type).select_from(Action).group_by(Type).all()

I've also tried using options(joinedload('type')), but I found out, this is only used to force eager loading and to bypass lazy-loading.

ADDITION: I've just added the backref in the relation of Entity. I think the problem is solvable by querying count(Type.entities) > 0, but I cannot figure out how to exactly form a valid ORM query.

Manuel Faux · Accepted Answer · 2010-11-01 18:40:13Z

1

I've just figured it out:

session.query(ActionType).filter(ActionType.actions.any()).all()

The any() does the trick.

answered Nov 1, 2010 at 18:40

Manuel Faux

2,4876 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlAlchemy Select Relation Type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related