1======================== 2Non-Traditional Mappings 3======================== 4 5.. _maptojoin: 6 7Mapping a Class against Multiple Tables 8======================================= 9 10Mappers can be constructed against arbitrary relational units (called 11*selectables*) in addition to plain tables. For example, the :func:`~.expression.join` 12function creates a selectable unit comprised of 13multiple tables, complete with its own composite primary key, which can be 14mapped in the same way as a :class:`.Table`:: 15 16 from sqlalchemy import Table, Column, Integer, \ 17 String, MetaData, join, ForeignKey 18 from sqlalchemy.ext.declarative import declarative_base 19 from sqlalchemy.orm import column_property 20 21 metadata = MetaData() 22 23 # define two Table objects 24 user_table = Table('user', metadata, 25 Column('id', Integer, primary_key=True), 26 Column('name', String), 27 ) 28 29 address_table = Table('address', metadata, 30 Column('id', Integer, primary_key=True), 31 Column('user_id', Integer, ForeignKey('user.id')), 32 Column('email_address', String) 33 ) 34 35 # define a join between them. This 36 # takes place across the user.id and address.user_id 37 # columns. 38 user_address_join = join(user_table, address_table) 39 40 Base = declarative_base() 41 42 # map to it 43 class AddressUser(Base): 44 __table__ = user_address_join 45 46 id = column_property(user_table.c.id, address_table.c.user_id) 47 address_id = address_table.c.id 48 49In the example above, the join expresses columns for both the 50``user`` and the ``address`` table. The ``user.id`` and ``address.user_id`` 51columns are equated by foreign key, so in the mapping they are defined 52as one attribute, ``AddressUser.id``, using :func:`.column_property` to 53indicate a specialized column mapping. Based on this part of the 54configuration, the mapping will copy 55new primary key values from ``user.id`` into the ``address.user_id`` column 56when a flush occurs. 57 58Additionally, the ``address.id`` column is mapped explicitly to 59an attribute named ``address_id``. This is to **disambiguate** the 60mapping of the ``address.id`` column from the same-named ``AddressUser.id`` 61attribute, which here has been assigned to refer to the ``user`` table 62combined with the ``address.user_id`` foreign key. 63 64The natural primary key of the above mapping is the composite of 65``(user.id, address.id)``, as these are the primary key columns of the 66``user`` and ``address`` table combined together. The identity of an 67``AddressUser`` object will be in terms of these two values, and 68is represented from an ``AddressUser`` object as 69``(AddressUser.id, AddressUser.address_id)``. 70 71 72Mapping a Class against Arbitrary Selects 73========================================= 74 75Similar to mapping against a join, a plain :func:`~.expression.select` object can be used with a 76mapper as well. The example fragment below illustrates mapping a class 77called ``Customer`` to a :func:`~.expression.select` which includes a join to a 78subquery:: 79 80 from sqlalchemy import select, func 81 82 subq = select([ 83 func.count(orders.c.id).label('order_count'), 84 func.max(orders.c.price).label('highest_order'), 85 orders.c.customer_id 86 ]).group_by(orders.c.customer_id).alias() 87 88 customer_select = select([customers, subq]).\ 89 select_from( 90 join(customers, subq, 91 customers.c.id == subq.c.customer_id) 92 ).alias() 93 94 class Customer(Base): 95 __table__ = customer_select 96 97Above, the full row represented by ``customer_select`` will be all the 98columns of the ``customers`` table, in addition to those columns 99exposed by the ``subq`` subquery, which are ``order_count``, 100``highest_order``, and ``customer_id``. Mapping the ``Customer`` 101class to this selectable then creates a class which will contain 102those attributes. 103 104When the ORM persists new instances of ``Customer``, only the 105``customers`` table will actually receive an INSERT. This is because the 106primary key of the ``orders`` table is not represented in the mapping; the ORM 107will only emit an INSERT into a table for which it has mapped the primary 108key. 109 110.. note:: 111 112 The practice of mapping to arbitrary SELECT statements, especially 113 complex ones as above, is 114 almost never needed; it necessarily tends to produce complex queries 115 which are often less efficient than that which would be produced 116 by direct query construction. The practice is to some degree 117 based on the very early history of SQLAlchemy where the :func:`.mapper` 118 construct was meant to represent the primary querying interface; 119 in modern usage, the :class:`.Query` object can be used to construct 120 virtually any SELECT statement, including complex composites, and should 121 be favored over the "map-to-selectable" approach. 122 123Multiple Mappers for One Class 124============================== 125 126In modern SQLAlchemy, a particular class is mapped by only one so-called 127**primary** mapper at a time. This mapper is involved in three main 128areas of functionality: querying, persistence, and instrumentation of the 129mapped class. The rationale of the primary mapper relates to the fact 130that the :func:`.mapper` modifies the class itself, not only 131persisting it towards a particular :class:`.Table`, but also :term:`instrumenting` 132attributes upon the class which are structured specifically according to the 133table metadata. It's not possible for more than one mapper 134to be associated with a class in equal measure, since only one mapper can 135actually instrument the class. 136 137However, there is a class of mapper known as the **non primary** mapper 138with allows additional mappers to be associated with a class, but with 139a limited scope of use. This scope typically applies to 140being able to load rows from an alternate table or selectable unit, but 141still producing classes which are ultimately persisted using the primary 142mapping. The non-primary mapper is created using the classical style 143of mapping against a class that is already mapped with a primary mapper, 144and involves the use of the :paramref:`~sqlalchemy.orm.mapper.non_primary` 145flag. 146 147The non primary mapper is of very limited use in modern SQLAlchemy, as the 148task of being able to load classes from subqueries or other compound statements 149can be now accomplished using the :class:`.Query` object directly. 150 151There is really only one use case for the non-primary mapper, which is that 152we wish to build a :func:`.relationship` to such a mapper; this is useful 153in the rare and advanced case that our relationship is attempting to join two 154classes together using many tables and/or joins in between. An example of this 155pattern is at :ref:`relationship_non_primary_mapper`. 156 157As far as the use case of a class that can actually be fully persisted 158to different tables under different scenarios, very early versions of 159SQLAlchemy offered a feature for this adapted from Hibernate, known 160as the "entity name" feature. However, this use case became infeasable 161within SQLAlchemy once the mapped class itself became the source of SQL 162expression construction; that is, the class' attributes themselves link 163directly to mapped table columns. The feature was removed and replaced 164with a simple recipe-oriented approach to accomplishing this task 165without any ambiguity of instrumentation - to create new subclasses, each 166mapped individually. This pattern is now available as a recipe at `Entity Name 167<http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName>`_. 168 169