1.. currentmodule:: sqlalchemy.orm 2 3.. _mapper_sql_expressions: 4 5SQL Expressions as Mapped Attributes 6==================================== 7 8Attributes on a mapped class can be linked to SQL expressions, which can 9be used in queries. 10 11Using a Hybrid 12-------------- 13 14The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called 15"hybrid attribute", 16described in the section :ref:`hybrids_toplevel`. The hybrid provides 17for an expression that works at both the Python level as well as at the 18SQL expression level. For example, below we map a class ``User``, 19containing attributes ``firstname`` and ``lastname``, and include a hybrid that 20will provide for us the ``fullname``, which is the string concatenation of the two:: 21 22 from sqlalchemy.ext.hybrid import hybrid_property 23 24 class User(Base): 25 __tablename__ = 'user' 26 id = Column(Integer, primary_key=True) 27 firstname = Column(String(50)) 28 lastname = Column(String(50)) 29 30 @hybrid_property 31 def fullname(self): 32 return self.firstname + " " + self.lastname 33 34Above, the ``fullname`` attribute is interpreted at both the instance and 35class level, so that it is available from an instance:: 36 37 some_user = session.query(User).first() 38 print(some_user.fullname) 39 40as well as usable within queries:: 41 42 some_user = session.query(User).filter(User.fullname == "John Smith").first() 43 44The string concatenation example is a simple one, where the Python expression 45can be dual purposed at the instance and class level. Often, the SQL expression 46must be distinguished from the Python expression, which can be achieved using 47:meth:`.hybrid_property.expression`. Below we illustrate the case where a conditional 48needs to be present inside the hybrid, using the ``if`` statement in Python and the 49:func:`_expression.case` construct for SQL expressions:: 50 51 from sqlalchemy.ext.hybrid import hybrid_property 52 from sqlalchemy.sql import case 53 54 class User(Base): 55 __tablename__ = 'user' 56 id = Column(Integer, primary_key=True) 57 firstname = Column(String(50)) 58 lastname = Column(String(50)) 59 60 @hybrid_property 61 def fullname(self): 62 if self.firstname is not None: 63 return self.firstname + " " + self.lastname 64 else: 65 return self.lastname 66 67 @fullname.expression 68 def fullname(cls): 69 return case([ 70 (cls.firstname != None, cls.firstname + " " + cls.lastname), 71 ], else_ = cls.lastname) 72 73.. _mapper_column_property_sql_expressions: 74 75Using column_property 76--------------------- 77 78The :func:`_orm.column_property` function can be used to map a SQL 79expression in a manner similar to a regularly mapped :class:`_schema.Column`. 80With this technique, the attribute is loaded 81along with all other column-mapped attributes at load time. This is in some 82cases an advantage over the usage of hybrids, as the value can be loaded 83up front at the same time as the parent row of the object, particularly if 84the expression is one which links to other tables (typically as a correlated 85subquery) to access data that wouldn't normally be 86available on an already loaded object. 87 88Disadvantages to using :func:`_orm.column_property` for SQL expressions include that 89the expression must be compatible with the SELECT statement emitted for the class 90as a whole, and there are also some configurational quirks which can occur 91when using :func:`_orm.column_property` from declarative mixins. 92 93Our "fullname" example can be expressed using :func:`_orm.column_property` as 94follows:: 95 96 from sqlalchemy.orm import column_property 97 98 class User(Base): 99 __tablename__ = 'user' 100 id = Column(Integer, primary_key=True) 101 firstname = Column(String(50)) 102 lastname = Column(String(50)) 103 fullname = column_property(firstname + " " + lastname) 104 105Correlated subqueries may be used as well. Below we use the :func:`_expression.select` 106construct to create a SELECT that links together the count of ``Address`` 107objects available for a particular ``User``:: 108 109 from sqlalchemy.orm import column_property 110 from sqlalchemy import select, func 111 from sqlalchemy import Column, Integer, String, ForeignKey 112 113 from sqlalchemy.ext.declarative import declarative_base 114 115 Base = declarative_base() 116 117 class Address(Base): 118 __tablename__ = 'address' 119 id = Column(Integer, primary_key=True) 120 user_id = Column(Integer, ForeignKey('user.id')) 121 122 class User(Base): 123 __tablename__ = 'user' 124 id = Column(Integer, primary_key=True) 125 address_count = column_property( 126 select([func.count(Address.id)]).\ 127 where(Address.user_id==id).\ 128 correlate_except(Address) 129 ) 130 131In the above example, we define a :func:`_expression.select` construct like the following:: 132 133 select([func.count(Address.id)]).\ 134 where(Address.user_id==id).\ 135 correlate_except(Address) 136 137The meaning of the above statement is, select the count of ``Address.id`` rows 138where the ``Address.user_id`` column is equated to ``id``, which in the context 139of the ``User`` class is the :class:`_schema.Column` named ``id`` (note that ``id`` is 140also the name of a Python built in function, which is not what we want to use 141here - if we were outside of the ``User`` class definition, we'd use ``User.id``). 142 143The :meth:`_expression.select.correlate_except` directive indicates that each element in the 144FROM clause of this :func:`_expression.select` may be omitted from the FROM list (that is, correlated 145to the enclosing SELECT statement against ``User``) except for the one corresponding 146to ``Address``. This isn't strictly necessary, but prevents ``Address`` from 147being inadvertently omitted from the FROM list in the case of a long string 148of joins between ``User`` and ``Address`` tables where SELECT statements against 149``Address`` are nested. 150 151If import issues prevent the :func:`.column_property` from being defined 152inline with the class, it can be assigned to the class after both 153are configured. In Declarative this has the effect of calling :meth:`_orm.Mapper.add_property` 154to add an additional property after the fact:: 155 156 User.address_count = column_property( 157 select([func.count(Address.id)]).\ 158 where(Address.user_id==User.id) 159 ) 160 161For a :func:`.column_property` that refers to columns linked from a 162many-to-many relationship, use :func:`.and_` to join the fields of the 163association table to both tables in a relationship:: 164 165 from sqlalchemy import and_ 166 167 class Author(Base): 168 # ... 169 170 book_count = column_property( 171 select( 172 [func.count(books.c.id)] 173 ).where( 174 and_( 175 book_authors.c.author_id==authors.c.id, 176 book_authors.c.book_id==books.c.id 177 ) 178 ) 179 ) 180 181.. _mapper_column_property_sql_expressions_composed: 182 183Composing from Column Properties at Mapping Time 184^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 185 186It is possible to create mappings that combine multiple 187:class:`.ColumnProperty` objects together. The :class:`.ColumnProperty` will 188be interpreted as a SQL expression when used in a Core expression context, 189provided that it is targeted by an existing expression object; this works by 190the Core detecting that the object has a ``__clause_element__()`` method which 191returns a SQL expression. However, if the :class:`.ColumnProperty` is used as 192a lead object in an expression where there is no other Core SQL expression 193object to target it, the :attr:`.ColumnProperty.expression` attribute will 194return the underlying SQL expression so that it can be used to build SQL 195expressions consistently. Below, the ``File`` class contains an attribute 196``File.path`` that concatenates a string token to the ``File.filename`` 197attribute, which is itself a :class:`.ColumnProperty`:: 198 199 200 class File(Base): 201 __tablename__ = 'file' 202 203 id = Column(Integer, primary_key=True) 204 name = Column(String(64)) 205 extension = Column(String(8)) 206 filename = column_property(name + '.' + extension) 207 path = column_property('C:/' + filename.expression) 208 209When the ``File`` class is used in expressions normally, the attributes 210assigned to ``filename`` and ``path`` are usable directly. The use of the 211:attr:`.ColumnProperty.expression` attribute is only necessary when using 212the :class:`.ColumnProperty` directly within the mapping definition:: 213 214 q = session.query(File.path).filter(File.filename == 'foo.txt') 215 216 217Using a plain descriptor 218------------------------ 219 220In cases where a SQL query more elaborate than what :func:`_orm.column_property` 221or :class:`.hybrid_property` can provide must be emitted, a regular Python 222function accessed as an attribute can be used, assuming the expression 223only needs to be available on an already-loaded instance. The function 224is decorated with Python's own ``@property`` decorator to mark it as a read-only 225attribute. Within the function, :func:`.object_session` 226is used to locate the :class:`.Session` corresponding to the current object, 227which is then used to emit a query:: 228 229 from sqlalchemy.orm import object_session 230 from sqlalchemy import select, func 231 232 class User(Base): 233 __tablename__ = 'user' 234 id = Column(Integer, primary_key=True) 235 firstname = Column(String(50)) 236 lastname = Column(String(50)) 237 238 @property 239 def address_count(self): 240 return object_session(self).\ 241 scalar( 242 select([func.count(Address.id)]).\ 243 where(Address.user_id==self.id) 244 ) 245 246The plain descriptor approach is useful as a last resort, but is less performant 247in the usual case than both the hybrid and column property approaches, in that 248it needs to emit a SQL query upon each access. 249 250.. _mapper_querytime_expression: 251 252Query-time SQL expressions as mapped attributes 253----------------------------------------------- 254 255When using :meth:`.Session.query`, we have the option to specify not just 256mapped entities but ad-hoc SQL expressions as well. Suppose if a class 257``A`` had integer attributes ``.x`` and ``.y``, we could query for ``A`` 258objects, and additionally the sum of ``.x`` and ``.y``, as follows:: 259 260 q = session.query(A, A.x + A.y) 261 262The above query returns tuples of the form ``(A object, integer)``. 263 264An option exists which can apply the ad-hoc ``A.x + A.y`` expression to the 265returned ``A`` objects instead of as a separate tuple entry; this is the 266:func:`.with_expression` query option in conjunction with the 267:func:`.query_expression` attribute mapping. The class is mapped 268to include a placeholder attribute where any particular SQL expression 269may be applied:: 270 271 from sqlalchemy.orm import query_expression 272 273 class A(Base): 274 __tablename__ = 'a' 275 id = Column(Integer, primary_key=True) 276 x = Column(Integer) 277 y = Column(Integer) 278 279 expr = query_expression() 280 281We can then query for objects of type ``A``, applying an arbitrary 282SQL expression to be populated into ``A.expr``:: 283 284 from sqlalchemy.orm import with_expression 285 q = session.query(A).options( 286 with_expression(A.expr, A.x + A.y)) 287 288The :func:`.query_expression` mapping has these caveats: 289 290* On an object where :func:`.query_expression` were not used to populate 291 the attribute, the attribute on an object instance will have the value 292 ``None``, unless the :paramref:`_orm.query_expression.default_expr` 293 parameter is set to an alternate SQL expression. 294 295* The query_expression value **does not populate on an object that is 296 already loaded**. That is, this will **not work**:: 297 298 obj = session.query(A).first() 299 300 obj = session.query(A).options(with_expression(A.expr, some_expr)).first() 301 302 To ensure the attribute is re-loaded, use :meth:`_orm.Query.populate_existing`:: 303 304 obj = session.query(A).populate_existing().options( 305 with_expression(A.expr, some_expr)).first() 306 307* The query_expression value **does not refresh when the object is 308 expired**. Once the object is expired, either via :meth:`.Session.expire` 309 or via the expire_on_commit behavior of :meth:`.Session.commit`, the value is 310 removed from the attribute and will return ``None`` on subsequent access. 311 Only by running a new :class:`_query.Query` that touches the object which includes 312 a new :func:`.with_expression` directive will the attribute be set to a 313 non-None value. 314 315* The mapped attribute currently **cannot** be applied to other parts of the 316 query, such as the WHERE clause, the ORDER BY clause, and make use of the 317 ad-hoc expression; that is, this won't work:: 318 319 # wont work 320 q = session.query(A).options( 321 with_expression(A.expr, A.x + A.y) 322 ).filter(A.expr > 5).order_by(A.expr) 323 324 The ``A.expr`` expression will resolve to NULL in the above WHERE clause 325 and ORDER BY clause. To use the expression throughout the query, assign to a 326 variable and use that:: 327 328 a_expr = A.x + A.y 329 q = session.query(A).options( 330 with_expression(A.expr, a_expr) 331 ).filter(a_expr > 5).order_by(a_expr) 332 333.. versionadded:: 1.2 334 335