Commits

Mike Bayer committed b93551d

- more docs
- some more query tests
- removed warnings from testbase to appease the buildbots

Comments (0)

Files changed (4)

doc/build/content/adv_datamapping.txt

 
     {python}
     query = session.query(Book)
-    query.options(defer('summary')).select()
-    query.options(undefer('excerpt')).select()
+    query.options(defer('summary')).all()
+    query.options(undefer('excerpt')).all()
 
 #### Working with Large Collections
 
     # order by multiple items
     mapper = mapper(User, users_table, order_by=[users_table.c.user_id, desc(users_table.c.user_name)])
 
-"order_by" can also be specified to an individual `select` method, overriding all other per-engine/per-mapper orderings:
+"order_by" can also be specified with queries, overriding all other per-engine/per-mapper orderings:
 
     {python}
     # order by a column
-    l = mapper.select(users_table.c.user_name=='fred', order_by=users_table.c.user_id)
+    l = query.filter(users_table.c.user_name=='fred').order_by(users_table.c.user_id).all()
     
     # order by multiple criterion
-    l = mapper.select(users_table.c.user_name=='fred', order_by=[users_table.c.user_id, desc(users_table.c.user_name)])
+    l = query.filter(users_table.c.user_name=='fred').order_by([users_table.c.user_id, desc(users_table.c.user_name)])
 
-For relations, the "order_by" property can also be specified to all forms of relation:
+The "order_by" property can also be specified on a `relation()` which will control the ordering of the collection:
 
     {python}
+    mapper(Address, addresses_table)
+    
     # order address objects by address id
-    mapper = mapper(User, users_table, properties = {
-        'addresses' : relation(mapper(Address, addresses_table), order_by=addresses_table.c.address_id)
+    mapper(User, users_table, properties = {
+        'addresses' : relation(Address, order_by=addresses_table.c.address_id)
     })
     
-    # eager load with ordering - the ORDER BY clauses of parent/child will be organized properly
-    mapper = mapper(User, users_table, properties = {
-        'addresses' : relation(mapper(Address, addresses_table), order_by=desc(addresses_table.c.email_address), lazy=False)
-    }, order_by=users_table.c.user_id)
     
-### Limiting Rows {@name=limits}
+### Limiting Rows Combined with Eager Loads {@name=limits}
 
-You can limit rows in a regular SQL query by specifying `limit` and `offset`.  A Mapper can handle the same concepts:
-
-    {python}
-    class User(object):
-        pass
-    
-    mapper(User, users_table)
-    {sql}r = session.query(User).select(limit=20, offset=10)
-    SELECT users.user_id AS users_user_id, 
-    users.user_name AS users_user_name, users.password AS users_password 
-    FROM users ORDER BY users.oid 
-    LIMIT 20 OFFSET 10
-    {}
-
-However, things get tricky when dealing with eager relationships, since a straight LIMIT of rows does not represent the count of items when joining against other tables to load related items as well.  So here is what SQLAlchemy will do when you use limit or offset with an eager relationship:
+As indicated in the docs on `Query`, you can limit rows using `limit()` and `offset()`.  However, things get tricky when dealing with eager relationships, since a straight LIMIT of rows will interfere with the eagerly-loaded rows.  So here is what SQLAlchemy will do when you use limit or offset with an eager relationship:
 
     {python}
     class User(object):
         mapper(User, users_table, properties={
         'addresses' : relation(mapper(Address, addresses_table), lazy=False)
     })
-    r = session.query(User).select(User.c.user_name.like('F%'), limit=20, offset=10)
+    r = session.query(User).filter(User.c.user_name.like('F%')).limit(20).offset(10).all()
     {opensql}SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, 
     users.password AS users_password, addresses.address_id AS addresses_address_id, 
     addresses.user_id AS addresses_user_id, addresses.street AS addresses_street, 
     WHERE rowcount.user_id = users.user_id ORDER BY users.oid, addresses.oid
     {'users_user_name': 'F%'}
     
-The main WHERE clause as well as the limiting clauses are coerced into a subquery; this subquery represents the desired result of objects.  A containing query, which handles the eager relationships, is joined against the subquery to produce the result.
+The main WHERE clause as well as the limiting clauses are coerced into a subquery; this subquery represents the desired result of objects.  A containing query, which handles the eager relationships, is joined against the subquery to produce the result.  This is something to keep in mind as it's a complex query which may be problematic on databases with poor support for LIMIT, such as Oracle which does not support it natively.
 
 ### Mapping a Class with Table Inheritance {@name=inheritance}
 
-Feature Status: [Alpha Implementation][alpha_implementation] 
+Inheritance in databases comes in three forms:  *single table inheritance*, where several types of classes are stored in one table, *concrete table inheritance*, where each type of class is stored in its own table, and *joined table inheritance*, where the parent/child classes are stored in their own tables that are joined together in a select.
 
-Inheritance in databases comes in three forms:  *single table inheritance*, where several types of classes are stored in one table, *concrete table inheritance*, where each type of class is stored in its own table, and *multiple table inheritance*, where the parent/child classes are stored in their own tables that are joined together in a select.
+There is also the ability to load "polymorphically", which is that a single query loads objects of multiple types at once.
 
-There is also a concept of `polymorphic` loading, which indicates if multiple kinds of classes can be loaded in one pass.
-
-SQLAlchemy supports all three kinds of inheritance.  Additionally, true `polymorphic` loading is supported in a straightfoward way for single table inheritance, and has some more manually-configured features that can make it happen for concrete and multiple table inheritance.
+SQLAlchemy supports all three kinds of inheritance.  Additionally, true "polymorphic" loading is supported in a straightfoward way for single table inheritance, and has some more manually-configured features that can make it happen for concrete and multiple table inheritance. 
 
 Working examples of polymorphic inheritance come with the distribution in the directory `examples/polymorphic`.
 
     manager_mapper = mapper(Manager, managers_table, inherits=employee_mapper, concrete=True, polymorphic_identity='manager')
     engineer_mapper = mapper(Engineer, engineers_table, inherits=employee_mapper, concrete=True, polymorphic_identity='engineer')
 
-A future release of SQLALchemy might better merge the generated UNION into the mapper construction phase.    
-
-#### Multiple Table Inheritance
+#### Joined Table Inheritance
 
 Like concrete table inheritance, this can be done non-polymorphically, or with a little more complexity, polymorphically:
 
     mapper(Engineer, engineers, inherits=person_mapper)
     mapper(Manager, managers, inherits=person_mapper)
 
-Polymorphic:
+Polymorphically, joined-table inheritance is easier than concrete, as a simple outer join can usually work:
 
-    {python title="Multiple Table Inheritance, Polymorphic"}
-    person_join = polymorphic_union(
-        {
-            'engineer':employees.join(engineers),
-            'manager':employees.join(managers),
-            'person':employees.select(employees.c.type=='person'),
-        }, None, 'pjoin')
+    {python title="Joined Table Inheritance, Polymorphic"}
+    person_join = people.outerjoin(engineers).outerjoin(managers)
 
-    person_mapper = mapper(Employee, employees, select_table=person_join, polymorphic_on=person_join.c.type, polymorphic_identity='person')
+    person_mapper = mapper(Person, people, select_table=person_join,polymorphic_on=people.c.type, polymorphic_identity='person')
     mapper(Engineer, engineers, inherits=person_mapper, polymorphic_identity='engineer')
     mapper(Manager, managers, inherits=person_mapper, polymorphic_identity='manager')
 
+In SQLAlchemy 0.4, the above mapper setup can load polymorphically *without* the join as well, by issuing distinct queries for each subclasses' table.
 
-The join condition in a multiple table inheritance relationship can be specified explicitly, using `inherit_condition`:
+The join condition in a joined table inheritance structure can be specified explicitly, using `inherit_condition`:
 
     {python}
     AddressUser.mapper = mapper(
 The "root" property on a TreeNode is a many-to-one relationship.  By default, a self-referential mapper declares relationships as one-to-many, so the extra parameter `remote_side`, pointing to a column or list of columns on the remote side of a relationship, is needed to indicate a "many-to-one" self-referring relationship (note the previous keyword argument `foreignkey` is deprecated).
 Both TreeNode examples above are available in functional form in the `examples/adjacencytree` directory of the distribution.    
 
-### Result-Set Mapping {@name=resultset}
+### Statement and Result-Set ORM Queries {@name=resultset}
 
-Take any result set and feed it into a Query to produce objects.  Multiple mappers can be combined to retrieve unrelated objects from the same row in one step.  The `instances` method on Query takes a ResultProxy object, which is the result type generated from SQLEngine, and delivers object instances.  (note: this method has been moved off of Mapper, where it is deprecated).
+Take any textual statement, constructed statement or result set and feed it into a Query to produce objects.  Below, we define two class/mapper combinations, issue a SELECT statement, and send the result object to the method `instances()` method on `Query`:
 
     {python}
     class User(object):
         pass
 
+    class Address(object):
+        pass
+
     mapper(User, users_table)
     
-    # select users
-    c = users_table.select().execute()
-
-    # get objects
-    userlist = session.query(User).instances(c)
-    
-    {python}
-    # define a second class/mapper
-    class Address(object):
-        pass
-        
     mapper(Address, addresses_table)
 
     # select users and addresses in one query
     # use_labels is so that the user_id column in both tables are distinguished
     s = select([users_table, addresses_table], users_table.c.user_id==addresses_table.c.user_id, use_labels=True)
 
-    # execute it, and process the results with the User mapper, chained to the Address mapper
-    r = session.query(User).instances(s.execute(), class_mapper(Address))
+    # execute it, and process the results, asking for both User and Address objects
+    r = session.query(User, Address).instances(s.execute())
     
-    # result rows are an array of objects, one for each mapper used
+    # result rows come back as tuples
     for entry in r:
         user = r[0]
         address = r[1]
 
-#### Combining Eager Loads with Result Set Mappings
+Alternatively, the `from_statement()` method may be used with either a textual string or SQL construct:
 
-When result-set mapping is used with a particular Mapper, SQLAlchemy has no access to the query being used and therefore has no way of tacking on its own `LEFT OUTER JOIN` conditions that are normally used to eager load relationships.  If the query being constructed is created in such a way that it returns rows not just from a parent table (or tables) but also returns rows from child tables, the result-set mapping can be notified as to which additional properties are contained within the result set.  This is done using the `contains_eager()` query option, which specifies the name of the relationship to be eagerly loaded, and optionally a **decorator function** that can translate aliased column names when results are received.
+    {python}
+    s = select([users_table, addresses_table], users_table.c.user_id==addresses_table.c.user_id, use_labels=True)
+
+    r = session.query(User, Address).from_statement(s).all()
+
+    for entry in r:
+        user = r[0]
+        address = r[1]
+
+#### Combining Eager Loads with Statement/Result Set Queries
+
+When statement is used with `Query`, SQLAlchemy does not affect the SQL query itself, and therefore has no way of tacking on its own `LEFT OUTER JOIN` conditions that are normally used to eager load relationships.  If the query being constructed is created in such a way that it returns rows not just from a parent table (or tables) but also returns rows from child tables, the result-set mapping can be notified as to which additional properties are contained within the result set.  This is done using the `contains_eager()` query option, which specifies the name of the relationship to be eagerly loaded.
 
     {python}
     # mapping is the users->addresses mapping
     # get results normally
     r = query.instances(statement.execute())
 
-It is often the case with large queries that some of the tables within the query need to be aliased in order to distinguish them from other occurences of the same table within the query.  A query that attempts to add eagerly loaded child items will often have this condition.  The `contains_eager()` function takes a keyword argument `alias` which can either be the string name of an alias, or an actual `Alias` construct used in constructing the query, which will target the eager loading towards the columns of that alias (new in version 0.3.5):
+If the "eager" portion of the statement is "alisaed", the `alias` keyword argument to `contains_eager()` may be used to indicate it.  This is a string alias name or reference to an actual `Alias` object:
 
     {python}
     # use an alias of the addresses table
     query = session.query(User).options(contains_eager('addresses', alias=adalias))
 
     # get results normally
-    {sql}r = query.instances(statement.execute())
+    {sql}r = query.from_statement(query).all()
     SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, adalias.address_id AS adalias_address_id, 
     adalias.user_id AS adalias_user_id, adalias.email_address AS adalias_email_address, (...other columns...)
     FROM users LEFT OUTER JOIN email_addresses AS adalias ON users.user_id = adalias.user_id
 
-In the case that the main table itself is also aliased, the `contains_alias()` option can be used (new in version 0.3.5):
+In the case that the main table itself is also aliased, the `contains_alias()` option can be used:
 
     {python}
     # define an aliased UNION called 'ulist'
     r = query.instances(statement.execute())
     
 
-### Mapper Options {@name=mapperoptions}
+### Mapper Keyword Arguments {@name=mapperoptions}
 
-Options which can be sent to the `mapper()` function.  For arguments to `relation()`, see [advdatamapping_properties_relationoptions](rel:advdatamapping_properties_relationoptions).
+Keyword arguments which can be used with the `mapper()` function.  For arguments to `relation()`, see [advdatamapping_properties_relationoptions](rel:advdatamapping_properties_relationoptions).
 
 * **allow_column_override** - if True, allows the usage of a `relation()` which has the same name as a column in the mapped table. The table column will no longer be mapped.
 * **allow_null_pks=False** - indicates that composite primary keys where one or more (but not all) columns contain NULL is a valid primary key. Primary keys which contain NULL values usually indicate that a result row does not contain an entity and should be skipped.

test/orm/inheritance.py

                 self.name = name
 
         class Employee(Person): pass
+
+        import warnings
+        warnings.filterwarnings("error", r".*On mapper.*distinct primary key")
     
     def insert_data(self):
         person_insert = person_table.insert()

test/orm/query.py

             assert fixtures.user_address_result == l
         self.assert_sql_count(testbase.db, go, 1)
 
+
+        def go():
+            l = q.options(contains_alias('ulist'), contains_eager('addresses')).from_statement(query).all()
+            assert fixtures.user_address_result == l
+        self.assert_sql_count(testbase.db, go, 1)
+
     def test_contains_eager(self):
 
         selectquery = users.outerjoin(addresses).select(use_labels=True, order_by=[users.c.id, addresses.c.id])
             assert fixtures.user_address_result == l
         self.assert_sql_count(testbase.db, go, 1)
 
+        def go():
+            l = q.options(contains_eager('addresses')).from_statement(selectquery).all()
+            assert fixtures.user_address_result == l
+        self.assert_sql_count(testbase.db, go, 1)
+
     def test_contains_eager_alias(self):
         adalias = addresses.alias('adalias')
         selectquery = users.outerjoin(adalias).select(use_labels=True, order_by=[users.c.id, adalias.c.id])
 from sqlalchemy.schema import MetaData
 from sqlalchemy.orm import clear_mappers
 
-import warnings
-warnings.filterwarnings("error")
-
 db = None
 metadata = None
 db_uri = None