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