1.. module:: sqlalchemy.orm
2
3.. _mapper_composite:
4
5Composite Column Types
6=======================
7
8Sets of columns can be associated with a single user-defined datatype. The ORM
9provides a single attribute which represents the group of columns using the
10class you provide.
11
12.. versionchanged:: 0.7
13    Composites have been simplified such that
14    they no longer "conceal" the underlying column based attributes.  Additionally,
15    in-place mutation is no longer automatic; see the section below on
16    enabling mutability to support tracking of in-place changes.
17
18.. versionchanged:: 0.9
19    Composites will return their object-form, rather than as individual columns,
20    when used in a column-oriented :class:`.Query` construct.  See :ref:`migration_2824`.
21
22A simple example represents pairs of columns as a ``Point`` object.
23``Point`` represents such a pair as ``.x`` and ``.y``::
24
25    class Point(object):
26        def __init__(self, x, y):
27            self.x = x
28            self.y = y
29
30        def __composite_values__(self):
31            return self.x, self.y
32
33        def __repr__(self):
34            return "Point(x=%r, y=%r)" % (self.x, self.y)
35
36        def __eq__(self, other):
37            return isinstance(other, Point) and \
38                other.x == self.x and \
39                other.y == self.y
40
41        def __ne__(self, other):
42            return not self.__eq__(other)
43
44The requirements for the custom datatype class are that it have a constructor
45which accepts positional arguments corresponding to its column format, and
46also provides a method ``__composite_values__()`` which returns the state of
47the object as a list or tuple, in order of its column-based attributes. It
48also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test
49the equality of two instances.
50
51We will create a mapping to a table ``vertices``, which represents two points
52as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column`
53objects. Then, the :func:`.composite` function is used to assign new
54attributes that will represent sets of columns via the ``Point`` class::
55
56    from sqlalchemy import Column, Integer
57    from sqlalchemy.orm import composite
58    from sqlalchemy.ext.declarative import declarative_base
59
60    Base = declarative_base()
61
62    class Vertex(Base):
63        __tablename__ = 'vertices'
64
65        id = Column(Integer, primary_key=True)
66        x1 = Column(Integer)
67        y1 = Column(Integer)
68        x2 = Column(Integer)
69        y2 = Column(Integer)
70
71        start = composite(Point, x1, y1)
72        end = composite(Point, x2, y2)
73
74A classical mapping above would define each :func:`.composite`
75against the existing table::
76
77    mapper(Vertex, vertices_table, properties={
78        'start':composite(Point, vertices_table.c.x1, vertices_table.c.y1),
79        'end':composite(Point, vertices_table.c.x2, vertices_table.c.y2),
80    })
81
82We can now persist and use ``Vertex`` instances, as well as query for them,
83using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances:
84
85.. sourcecode:: python+sql
86
87    >>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
88    >>> session.add(v)
89    >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
90    {sql}>>> print(q.first().start)
91    BEGIN (implicit)
92    INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?)
93    (3, 4, 5, 6)
94    SELECT vertices.id AS vertices_id,
95            vertices.x1 AS vertices_x1,
96            vertices.y1 AS vertices_y1,
97            vertices.x2 AS vertices_x2,
98            vertices.y2 AS vertices_y2
99    FROM vertices
100    WHERE vertices.x1 = ? AND vertices.y1 = ?
101     LIMIT ? OFFSET ?
102    (3, 4, 1, 0)
103    {stop}Point(x=3, y=4)
104
105.. autofunction:: composite
106
107
108Tracking In-Place Mutations on Composites
109-----------------------------------------
110
111In-place changes to an existing composite value are
112not tracked automatically.  Instead, the composite class needs to provide
113events to its parent object explicitly.   This task is largely automated
114via the usage of the :class:`.MutableComposite` mixin, which uses events
115to associate each user-defined composite object with all parent associations.
116Please see the example in :ref:`mutable_composites`.
117
118.. versionchanged:: 0.7
119    In-place changes to an existing composite value are no longer
120    tracked automatically; the functionality is superseded by the
121    :class:`.MutableComposite` class.
122
123.. _composite_operations:
124
125Redefining Comparison Operations for Composites
126-----------------------------------------------
127
128The "equals" comparison operation by default produces an AND of all
129corresponding columns equated to one another. This can be changed using
130the ``comparator_factory`` argument to :func:`.composite`, where we
131specify a custom :class:`.CompositeProperty.Comparator` class
132to define existing or new operations.
133Below we illustrate the "greater than" operator, implementing
134the same expression that the base "greater than" does::
135
136    from sqlalchemy.orm.properties import CompositeProperty
137    from sqlalchemy import sql
138
139    class PointComparator(CompositeProperty.Comparator):
140        def __gt__(self, other):
141            """redefine the 'greater than' operation"""
142
143            return sql.and_(*[a>b for a, b in
144                              zip(self.__clause_element__().clauses,
145                                  other.__composite_values__())])
146
147    class Vertex(Base):
148        ___tablename__ = 'vertices'
149
150        id = Column(Integer, primary_key=True)
151        x1 = Column(Integer)
152        y1 = Column(Integer)
153        x2 = Column(Integer)
154        y2 = Column(Integer)
155
156        start = composite(Point, x1, y1,
157                            comparator_factory=PointComparator)
158        end = composite(Point, x2, y2,
159                            comparator_factory=PointComparator)
160
161