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