I've never been a fan of Rails' hieroglyphics. "The query is the one you’d hope for" - why do we need to "hope", when the ORM could just allow you to use relational concepts directly ? Here is SQLAlchemy's much less exciting version of what I see here for "merge", just use a hybrid (sorry, we have more verbose config, due to explicit is better than implicit):<p><pre><code> from sqlalchemy import Column, Integer, String, ForeignKey, Enum
from sqlalchemy.orm import Session, relationship, backref
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.hybrid import hybrid_property
class Base(object):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
Base = declarative_base(cls=Base)
class SurrogatePK(object):
id = Column(Integer, primary_key=True)
class Article(SurrogatePK, Base):
headline = Column(String)
@property
def users(self):
return self.collaborations.join("user").with_entities(User)
class User(SurrogatePK, Base):
name = Column(String)
class Collaboration(Base):
article_id = Column(ForeignKey('article.id'),
primary_key=True)
user_id = Column(ForeignKey('user.id'),
primary_key=True)
role = Column(Enum('editor', 'author'))
user = relationship("User", backref="collaborations")
article = relationship("Article",
backref=backref("collaborations", lazy="dynamic"))
@hybrid_property
def editorial(self):
return self.role == 'editor'
sess = Session()
some_article = Article(id=5)
sess.add(some_article)
print some_article.users.filter(Collaboration.editorial)
</code></pre>
you get the same "one line, DRY" calling style at the end and equivalent SQL:<p><pre><code> SELECT "user".id AS user_id, "user".name AS user_name
FROM collaboration JOIN "user" ON "user".id = collaboration.user_id
WHERE :param_1 = collaboration.article_id AND collaboration.role = :role_1</code></pre>