Add join and outerjoin classmethods to declarative classes

[Haleemur Ali]

Given a database described via declarative syntax

from sqlalchemy import select, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    street = Column(String)
    city = Column(String)
    postcode = Column(String)
    region = Column(String)

The following query written in sqlalchemy expression language is not valid:

stmt = select(
    [User.name, Address.city]
).select_from(User.join(Address, User.id == Address.user_id))

It raises AttributeError: type object 'User' has no attribute 'join'.

However, the same expression written using User.__table__ is valid, i.e.

>>>str(User.__table__.join(Address, User.id == Address.user_id))
'users JOIN addresses ON users.id = addresses.user_id'

produces the desired join expression.

The join & outerjoin functions imported from sqlalchemy.sql.expression accept declarative classes as input and is able to produce the desired output

>>>from sqlalchemy.sql.expression import join, outerjoin
>>>str(join(User, Address, User.id == Address.user_id))
'users JOIN addresses ON users.id = addresses.user_id'
>>>str(outerjoin(User, Address, User.id == Address.user_id))
'users LEFT OUTER JOIN addresses ON users.id = addresses.user_id'

In my opinion this is a simple fix to add join & outerjoin functions as classmethods to declarative classes through the mapper. That way both classically declared mappings and modern declarative mappings will be consistently usable in sqlalchemy expression language.

I'd be happy to submit a PR for this change.

[Mike Bayer]

Hi -

thanks for this suggestion! Unfortunately, such a change is not appropriate across the board, however it is trivial to provide join/outerjoin methods in your own projects using declarative mixins.

The Table object and its other relatives all descend from a common base FromClause. Table has not just join() and outerjoin() methods but also count(), select(), alias(), is_derived_from(), correspond_on_equivalents(), corresponding_columns(), .description, .columns, .primary_key, .foreign_keys. At the same time, a declarative mapped class can also be mapped to a Select object directly, so that object adds methods like cte(), label(), union(), union_all(), intersect(), etc. all of which could apply to this declarative mapped class as well. It doesn't make sense to single out just "join()" and "outerjoin()" as preferred "selectable" methods to put on declarative classes and not all the others, if the goal is that the mapping is "consistently usable in the SQLA expression language". The chance that some of these method names would conflict with existing names on someone's declarative class is high, and it further means that we no longer could add new methods to Table or Select without adding them to declarative classes which would each time break someone's application.

The design decision that mapped classes have no methods or attributes on them whatsoever is actually an original SQLAlchemy design concept for this reason, and it's also why Table and other selectable objects expose the columns themselves via a .c attribute; avoiding designs that build in naming conflict issues is a core design practice throughout SQLAlchemy.

Providing join() and outerjoin() methods on your own declarative mapped classes is trivial by using a mixin:

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import join, outerjoin


class ProvidesJoin(object):
    @classmethod
    def join(cls, other, onclause=None):
        return join(cls, other, onclause)

    @classmethod
    def outerjoin(cls, other, onclause=None):
        return outerjoin(cls, other, onclause)


Base = declarative_base(cls=ProvidesJoin)


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

print A.join(B)