Non-Traditional Mappings

Mapping a Class against Multiple Tables

Mappers can be constructed against arbitrary relational units (calledselectables) in addition to plain tables. For example, the join()function creates a selectable unit comprised ofmultiple tables, complete with its own composite primary key, which can bemapped in the same way as a Table:

from sqlalchemy import Table, Column, Integer, \
        String, MetaData, join, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property
 
metadata = MetaData()
 
# define two Table objects
user_table = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String),
        )
 
address_table = Table('address', metadata,
            Column('id', Integer, primary_key=True),
            Column('user_id', Integer, ForeignKey('user.id')),
            Column('email_address', String)
            )
 
# define a join between them.  This
# takes place across the user.id and address.user_id
# columns.
user_address_join = join(user_table, address_table)
 
Base = declarative_base()
 
# map to it
class AddressUser(Base):
    __table__ = user_address_join
 
    id = column_property(user_table.c.id, address_table.c.user_id)
    address_id = address_table.c.id

In the example above, the join expresses columns for both theuser and the address table. The user.id and address.user_idcolumns are equated by foreign key, so in the mapping they are definedas one attribute, AddressUser.id, using column_property() toindicate a specialized column mapping. Based on this part of theconfiguration, the mapping will copynew primary key values from user.id into the address.user_id columnwhen a flush occurs.

Additionally, the address.id column is mapped explicitly toan attribute named address_id. This is to disambiguate themapping of the address.id column from the same-named AddressUser.idattribute, which here has been assigned to refer to the user tablecombined with the address.user_id foreign key.

The natural primary key of the above mapping is the composite of(user.id, address.id), as these are the primary key columns of theuser and address table combined together. The identity of anAddressUser object will be in terms of these two values, andis represented from an AddressUser object as(AddressUser.id, AddressUser.address_id).

Mapping a Class against Arbitrary Selects

Similar to mapping against a join, a plain select() object can be used with amapper as well. The example fragment below illustrates mapping a classcalled Customer to a select() which includes a join to asubquery:

from sqlalchemy import select, func
 
subq = select([
            func.count(orders.c.id).label('order_count'),
            func.max(orders.c.price).label('highest_order'),
            orders.c.customer_id
            ]).group_by(orders.c.customer_id).alias()
 
customer_select = select([customers, subq]).\
            select_from(
                join(customers, subq,
                        customers.c.id == subq.c.customer_id)
            ).alias()
 
class Customer(Base):
    __table__ = customer_select

Above, the full row represented by customer_select will be all thecolumns of the customers table, in addition to those columnsexposed by the subq subquery, which are order_count,highest_order, and customer_id. Mapping the Customerclass to this selectable then creates a class which will containthose attributes.

When the ORM persists new instances of Customer, only thecustomers table will actually receive an INSERT. This is because theprimary key of the orders table is not represented in the mapping; the ORMwill only emit an INSERT into a table for which it has mapped the primarykey.

Note

The practice of mapping to arbitrary SELECT statements, especiallycomplex ones as above, isalmost never needed; it necessarily tends to produce complex querieswhich are often less efficient than that which would be producedby direct query construction. The practice is to some degreebased on the very early history of SQLAlchemy where the mapper()construct was meant to represent the primary querying interface;in modern usage, the Query object can be used to constructvirtually any SELECT statement, including complex composites, and shouldbe favored over the “map-to-selectable” approach.

Multiple Mappers for One Class

In modern SQLAlchemy, a particular class is mapped by only one so-calledprimary mapper at a time. This mapper is involved in three mainareas of functionality: querying, persistence, and instrumentation of themapped class. The rationale of the primary mapper relates to the factthat the mapper() modifies the class itself, not onlypersisting it towards a particular Table, but also instrumentingattributes upon the class which are structured specifically according to thetable metadata. It’s not possible for more than one mapperto be associated with a class in equal measure, since only one mapper canactually instrument the class.

However, there is a class of mapper known as the non primary mapperwhich allows additional mappers to be associated with a class, but witha limited scope of use. This scope typically applies tobeing able to load rows from an alternate table or selectable unit, butstill producing classes which are ultimately persisted using the primarymapping. The non-primary mapper is created using the classical styleof mapping against a class that is already mapped with a primary mapper,and involves the use of the non_primaryflag.

The non primary mapper is of very limited use in modern SQLAlchemy, as thetask of being able to load classes from subqueries or other compound statementscan be now accomplished using the Query object directly.

There is really only one use case for the non-primary mapper, which is thatwe wish to build a relationship() to such a mapper; this is usefulin the rare and advanced case that our relationship is attempting to join twoclasses together using many tables and/or joins in between. An example of thispattern is at relationship_non_primary_mapper.

As far as the use case of a class that can actually be fully persistedto different tables under different scenarios, very early versions ofSQLAlchemy offered a feature for this adapted from Hibernate, knownas the “entity name” feature. However, this use case became infeasablewithin SQLAlchemy once the mapped class itself became the source of SQLexpression construction; that is, the class’ attributes themselves linkdirectly to mapped table columns. The feature was removed and replacedwith a simple recipe-oriented approach to accomplishing this taskwithout any ambiguity of instrumentation - to create new subclasses, eachmapped individually. This pattern is now available as a recipe at Entity Name.