SQLAlchemy - Using Relationships to filter by class of row type

Question

Below there is a pseudo Many-to-Many table structure of say a customer ordering an event. What I would ideally like to be able to achieve is do implicit queries and sets by using the different relationship names.

For example (presume that the init code creates the associated objects)

order=order()
order.events.corporate.name = "Big Corp"

This would set the event.type (enum) to "corporate" and the event.name to "Big Corp". Similarly.

print session.query(Order).filter(order.events.corporate.name == "Big Corp")

This would only find the event record that had the associated event.type = "corporate". (Trivial example and not necessary but you get the idea)

Similarly using order.events.personal.name would set/query the corresponding records with event.type = "personal"

Your help in understanding what is the best way of achieving this functionality would be much appreciated.

Base = declarative_base()

class Order(Base):
    __tablename__ = 'order'
    id                  = Column(Integer, primary_key=True)
    event_id            = Column(Integer, ForeignKey('events.id'))

    events = relationship("Events")

class Events(Base):
    __tablename__ = 'events'
    order_id            = Column(Integer, ForeignKey('order.id'), primary_key=True)
    event_id            = Column(Integer, ForeignKey('event.id'), primary_key=True)

    corporate = relationship("Event")
    personal  = relationship("Event")
    event     = relationship("Event")

class Event(Base):
    __tablename__ = 'event'
    id                  = Column(Integer, primary_key=True)
    type                = Column(Enum('corporate', 'personal', name='enum_ev_type'))
    name                = Column(String(32))

Answer 1

SQLAlchemy do not understand the case when relationship is used in filter. In your example order.events.corporate.

I've got the following exception while trying to do so:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Order.events has an attribute 'corporate'

I would suggest to consider using AssociationObject pattern described in SQL Alchemy Relationships documentation page.

So the query would be:

session.query(Order).filter(and_(EventsAssoc.type=="corporate",Event.name=="Big Corporation"))

See full example how to define schema and create objects.

from sqlalchemy import *
from sqlalchemy import create_engine, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship



metadata = MetaData()
Base = declarative_base()
Base.metadata = metadata



class Event(Base):
    __tablename__ = 'event'
    id                  = Column(Integer, Sequence("event_seq"), primary_key=True)
    name                = Column(String(32))
    def __repr__(self):
        return "%s(name=\"%s\",id=\"%s\")" % (self.__class__.__name__,self.name,self.id)


class EventsAssoc(Base):
    __tablename__ = 'events'
    id                  = Column(Integer, Sequence("events_seq"), primary_key=True)
    left_id = Column(Integer, ForeignKey('order.id'))
    right_id = Column(Integer, ForeignKey('event.id'))

#    order_id            = Column(Integer, ForeignKey('order.id'), primary_key=True)
#    event_id            = Column(Integer, ForeignKey('event.id'), primary_key=True)
    type                 = Column(Enum('corporate', 'personal', name='enum_ev_type'))

    event = relationship(Event, backref="order_assocs")

    def __repr__(self):
        return "%s(events=%r,id=\"%s\")" % (self.__class__.__name__,self.event,self.id)


class Order(Base):
    __tablename__ = 'order'
    id                  = Column(Integer, Sequence("order_seq"), primary_key=True)
    name = Column(String(127))
    events = relationship(EventsAssoc)
    def __repr__(self):
        return "%s(name=\"%s\",id=\"%s\")" % (self.__class__.__name__,self.name,self.id)


db = create_engine('sqlite:////temp/test_assoc.db',echo=True)



#making sure we are working with a fresh database
metadata.drop_all(db)
metadata.create_all(db)


sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

o = Order(name="order1")
ea_corp = EventsAssoc(type="corporate")
ea_corp.event = Event(name="Big Corporation")

ea_pers = EventsAssoc(type="personal")
ea_pers.event = Event(name="Person")


o.events.append(ea_corp)
o.events.append(ea_pers)
session.add(o)
session.flush()

query = session.query(Order).filter(and_(EventsAssoc.type=="corporate",Event.name=="Big Corporation"))

for order in query.all():
    print order
    print order.events

Here is the query produced by sqlalchemy:

SELECT "order".id AS order_id, "order".name AS order_name 
FROM "order", events, event 
WHERE events.type = ? AND event.name = ?
('corporate', 'Big Corporation')

PS To enhance the association object pattern such that direct access to the EventsAssoc object is optional, SQLAlchemy provides the Association Proxy extension