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 12A simple example represents pairs of columns as a ``Point`` object. 13``Point`` represents such a pair as ``.x`` and ``.y``:: 14 15 class Point(object): 16 def __init__(self, x, y): 17 self.x = x 18 self.y = y 19 20 def __composite_values__(self): 21 return self.x, self.y 22 23 def __repr__(self): 24 return "Point(x=%r, y=%r)" % (self.x, self.y) 25 26 def __eq__(self, other): 27 return isinstance(other, Point) and \ 28 other.x == self.x and \ 29 other.y == self.y 30 31 def __ne__(self, other): 32 return not self.__eq__(other) 33 34The requirements for the custom datatype class are that it have a constructor 35which accepts positional arguments corresponding to its column format, and 36also provides a method ``__composite_values__()`` which returns the state of 37the object as a list or tuple, in order of its column-based attributes. It 38also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test 39the equality of two instances. 40 41We will create a mapping to a table ``vertices``, which represents two points 42as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column` 43objects. Then, the :func:`.composite` function is used to assign new 44attributes that will represent sets of columns via the ``Point`` class:: 45 46 from sqlalchemy import Column, Integer 47 from sqlalchemy.orm import composite 48 from sqlalchemy.ext.declarative import declarative_base 49 50 Base = declarative_base() 51 52 class Vertex(Base): 53 __tablename__ = 'vertices' 54 55 id = Column(Integer, primary_key=True) 56 x1 = Column(Integer) 57 y1 = Column(Integer) 58 x2 = Column(Integer) 59 y2 = Column(Integer) 60 61 start = composite(Point, x1, y1) 62 end = composite(Point, x2, y2) 63 64A classical mapping above would define each :func:`.composite` 65against the existing table:: 66 67 mapper(Vertex, vertices_table, properties={ 68 'start':composite(Point, vertices_table.c.x1, vertices_table.c.y1), 69 'end':composite(Point, vertices_table.c.x2, vertices_table.c.y2), 70 }) 71 72We can now persist and use ``Vertex`` instances, as well as query for them, 73using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances: 74 75.. sourcecode:: python+sql 76 77 >>> v = Vertex(start=Point(3, 4), end=Point(5, 6)) 78 >>> session.add(v) 79 >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4)) 80 {sql}>>> print(q.first().start) 81 BEGIN (implicit) 82 INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?) 83 (3, 4, 5, 6) 84 SELECT vertices.id AS vertices_id, 85 vertices.x1 AS vertices_x1, 86 vertices.y1 AS vertices_y1, 87 vertices.x2 AS vertices_x2, 88 vertices.y2 AS vertices_y2 89 FROM vertices 90 WHERE vertices.x1 = ? AND vertices.y1 = ? 91 LIMIT ? OFFSET ? 92 (3, 4, 1, 0) 93 {stop}Point(x=3, y=4) 94 95.. autofunction:: composite 96 97 98Tracking In-Place Mutations on Composites 99----------------------------------------- 100 101In-place changes to an existing composite value are 102not tracked automatically. Instead, the composite class needs to provide 103events to its parent object explicitly. This task is largely automated 104via the usage of the :class:`.MutableComposite` mixin, which uses events 105to associate each user-defined composite object with all parent associations. 106Please see the example in :ref:`mutable_composites`. 107 108.. _composite_operations: 109 110Redefining Comparison Operations for Composites 111----------------------------------------------- 112 113The "equals" comparison operation by default produces an AND of all 114corresponding columns equated to one another. This can be changed using 115the ``comparator_factory`` argument to :func:`.composite`, where we 116specify a custom :class:`.CompositeProperty.Comparator` class 117to define existing or new operations. 118Below we illustrate the "greater than" operator, implementing 119the same expression that the base "greater than" does:: 120 121 from sqlalchemy.orm.properties import CompositeProperty 122 from sqlalchemy import sql 123 124 class PointComparator(CompositeProperty.Comparator): 125 def __gt__(self, other): 126 """redefine the 'greater than' operation""" 127 128 return sql.and_(*[a>b for a, b in 129 zip(self.__clause_element__().clauses, 130 other.__composite_values__())]) 131 132 class Vertex(Base): 133 ___tablename__ = 'vertices' 134 135 id = Column(Integer, primary_key=True) 136 x1 = Column(Integer) 137 y1 = Column(Integer) 138 x2 = Column(Integer) 139 y2 = Column(Integer) 140 141 start = composite(Point, x1, y1, 142 comparator_factory=PointComparator) 143 end = composite(Point, x2, y2, 144 comparator_factory=PointComparator) 145 146Nesting Composites 147------------------- 148 149Composite objects can be defined to work in simple nested schemes, by 150redefining behaviors within the composite class to work as desired, then 151mapping the composite class to the full length of individual columns normally. 152Typically, it is convenient to define separate constructors for user-defined 153use and generate-from-row use. Below we reorganize the ``Vertex`` class to 154itself be a composite object, which is then mapped to a class ``HasVertex``:: 155 156 from sqlalchemy.orm import composite 157 158 class Point(object): 159 def __init__(self, x, y): 160 self.x = x 161 self.y = y 162 163 def __composite_values__(self): 164 return self.x, self.y 165 166 def __repr__(self): 167 return "Point(x=%r, y=%r)" % (self.x, self.y) 168 169 def __eq__(self, other): 170 return isinstance(other, Point) and \ 171 other.x == self.x and \ 172 other.y == self.y 173 174 def __ne__(self, other): 175 return not self.__eq__(other) 176 177 class Vertex(object): 178 def __init__(self, start, end): 179 self.start = start 180 self.end = end 181 182 @classmethod 183 def _generate(self, x1, y1, x2, y2): 184 """generate a Vertex from a row""" 185 return Vertex( 186 Point(x1, y1), 187 Point(x2, y2) 188 ) 189 190 def __composite_values__(self): 191 return \ 192 self.start.__composite_values__() + \ 193 self.end.__composite_values__() 194 195 class HasVertex(Base): 196 __tablename__ = 'has_vertex' 197 id = Column(Integer, primary_key=True) 198 x1 = Column(Integer) 199 y1 = Column(Integer) 200 x2 = Column(Integer) 201 y2 = Column(Integer) 202 203 vertex = composite(Vertex._generate, x1, y1, x2, y2) 204 205We can then use the above mapping as:: 206 207 hv = HasVertex(vertex=Vertex(Point(1, 2), Point(3, 4))) 208 209 s.add(hv) 210 s.commit() 211 212 hv = s.query(HasVertex).filter( 213 HasVertex.vertex == Vertex(Point(1, 2), Point(3, 4))).first() 214 print(hv.vertex.start) 215 print(hv.vertex.end) 216