Column Correspondence

This is the ability to relate the columns in one SQL expression to another. Consider these two expressions; each ultimately select from the same table and return the same results:

SELECT id, name FROM usertable

SELECT ua.uid, ua.uname from (select id as uid, name as uname from usertable) AS ua

When executed, the cursor.description of each statement contains the names of the result columns. The first statement will have the names id and name, whereas the second will have the names uid and uname. Column correspondence allows us to match id to uid and name to uname. Starting with the table definition and expressions for these two selects:

>>> from sqlalchemy import *
>>> meta = MetaData()
>>> users = Table('usertable', meta,
...     Column('id', Integer, primary_key=True),
...     Column('name', String(50)),
...     )

>>> s1 = select([users.c.id.label('uid'), users.c.id.label('uname')])
>>> ua = s1.alias('ua')
>>> s2 = select([ua.c.uid, ua.c.uname])

The columns in the two selects can be related to the base table as well as each other using corresponding_column():

>>> print users.corresponding_column(s2.c.uid)
usertable.id
>>> print s2.corresponding_column(s1.c.uname)
uname

With a so-called "composite" expression that contains multiple SELECT statements, the behavior is a little more intricate, as in the UNION below which takes two different tables and melds them together:

SELECT id AS uid, name AS uname FROM (
    SELECT id, name FROM usertable WHERE name='jack'

    UNION ALL

    SELECT id, email_address FROM addresses WHERE id=12
)

The representation in expression form, adding in the addresses table to start:

>>> addresses = Table('addresses', meta,
...     Column('id', Integer, primary_key=True),
...     Column('email_address', String(100)),
...     Column('user_id', Integer, ForeignKey('usertable.id')))

>>> s1 = users.select().where(users.c.name=='jack')
>>> s2 = select([addresses.c.id, addresses.c.email_address]).where(addresses.c.id==12)
>>> u = union_all(s1, s2)
>>> s3 = select([u.c.id.label('uid'), u.c.name.label('uname')])

Now, what column does s3.c.uid correspond to ? In the above union, its derived from both users.c.id and addresses.c.id All the paths you'd expect work:

>>> print addresses.corresponding_column(s3.c.uid)
addresses.id
>>> print users.corresponding_column(s3.c.uid)
usertable.id
>>> print addresses.corresponding_column(s3.c.uname)
addresses.email_address
>>> print s3.corresponding_column(users.c.id)
uid

The corresponding_column() method makes usage of a set associated with every column called proxy_set. Whenever a column is derived from another, it's given a collection called proxies which contains the originating column, or in the case of a union, all originators. proxy_set is then created on-demand and is the union of the current column's proxies collection with the proxies collection of each column in that collection, recursively up to the original column; basically a linked list. To see if the paths of two columns c1 and c2 overlap, we check if the intersection of c1.proxy_set and c2.proxy_set is non-empty. Multiple hits can be resolved by taking the largest intersection.

Column correspondence is used all over the place in SQLAlchemy. One way it's used is to adapt result rows from one selectable to another. Suppose we plugged into a test database, and created the standard "adjacency list" table nodes:

>>> meta.bind = create_engine('sqlite://')
>>> nodes = Table('nodes', meta,
...     Column('node_id', Integer, primary_key=True),
...     Column('parent_id', Integer, ForeignKey('nodes.node_id')),
...     Column('data', String(50)))
>>> nodes.create()

Then, we want to load all nodes, as well as their children and grandchildren, using outer joins. To join a table to itself requires aliases, such as this SQL:

SELECT nodes.node_id, nodes.parent_id, nodes.data,
       n2.node_id, n2.parent_id, n2.data,
       n3.node_id, n3.parent_id, n3.data
FROM
       nodes
       LEFT OUTER JOIN nodes AS n2 ON nodes.node_id=n2.parent_id
       LEFT OUTER JOIN nodes AS n3 ON n2.node_id=n3.parent_id

Then suppose we want a function that can print out the attributes of a "node" for us, given a result row. If we were just selecting three columns, we could do this:

>>> def print_node(row):
...    print "Id:", row[0], "Parent id:", row[1], "Data:", row[2]

We can see that approach won't work for our query above, as we need to locate three sets of columns at different positions. While we could try passing in numerical clues as to where the columns we want are, or rely upon naming schemes, SQLAlchemy most cleanly abstracts away column targeting by allowing you to use the actual Column objects which comprise the expression's "columns" clause. Building our query as an expression:

>>> n2 = nodes.alias('n2')
>>> n3 = nodes.alias('n3')
>>> all_nodes = nodes.outerjoin(n2, nodes.c.node_id==n2.c.parent_id).\
...     outerjoin(n3, n2.c.node_id==n3.c.parent_id).select(use_labels=True)

The use_labels sets up unique names for each column, which is a current "implementation detail"; SQLAlchemy targets columns ultimately based on string names, so they need to be unique. While positional targeting would remove this detail, concerns over free-text expressions which might throw off the column count have so far prevented this switch.

We would want to define our print_node as below, referencing only the node table. The function has no awareness of the n2 and n3 alias constructs:

>>> def print_node(row):
...    print "Id:", row[nodes.c.node_id], \
...    "Parent id:", row[nodes.c.parent_id], \
...    "Data:", row[nodes.c.data]

To differentiate among columns that correspond to nodes, n2, or n3, SQLAlchemy uses a function built on top of corresponding_column called row_adapter, which returns a callable that can translate a row from one selectable to another. So usage would look like this:

>>> from sqlalchemy.sql.util import row_adapter
>>> n2_adapter = row_adapter(n2, nodes)
>>> n3_adapter = row_adapter(n3, nodes)
>>> for row in all_nodes.execute():
...    print_node(row)
...    print_node(n2_adapter(row))
...    print_node(n3_adapter(row))

print_node is used three times with the same row, each time given a different interpretation of that row provided by the adapter. What row_adapter() does here is provide a decoupling layer between code which knows how a selectable was constructed and code which consumes result rows from that selectable.

A more wacky example. Suppose we have a regular mapping between User and Address, with a one-to-many addresses matched by a many-to-one users. This is the same example in the SQLAlchemy ORM tutorial and dozens of unit tests:

>>> from sqlalchemy.orm import *
>>> class User(object):pass
>>> class Address(object):pass
>>> mapper(User, users, properties={'addresses':relation(Address, backref='user')})
>>> mapper(Address, addresses)

So the DBAs send you a large UNION query that returns for you one address record per user id represented in the addresses table; either the address row that has the domain "@blooger.biz", or if no such address exists for that user, the address row with the highest primary key value. The query below accomplishes this by selecting first all the "blooger.biz" rows, then unioning that result with the "max id" result which explicitly omits the "blogger.biz" rows:

SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses WHERE
addresses.email_address.like('%@blooger.biz')

UNION ALL

SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses JOIN
(SELECT MAX(addresses.id) AS maxid
  FROM addresses GROUP BY addresses.user_id) AS max_ids
ON addresses.id=max_ids.maxid
WHERE NOT EXISTS(SELECT 1 FROM addresses WHERE
       user_id=addresses.user_id AND
       addresses.email_address.like('%@blooger.biz'))

How do we "drop in" this select statement into an ORM query with a minimum of headache ? We could certainly work from the string representation above directly, just say query.from_statement(<text>) and be done with it. But we're going to want to do more with this query in the next section, where the Query will be able to intelligently manipulate the expression further, so for that we build a SQL construct out of it. We build such an expression the same way any DBA would design the above SQL directly, working from the inside out:

>>> max_ids = select([func.max(addresses.c.id).label('maxid')]).\
...       group_by(addresses.c.user_id).alias('max_ids')
>>> aalias = addresses.alias()
>>> aview = union_all(
...  addresses.select().where(addresses.c.email_address.endswith('@blooger.biz')),
...  addresses.select().\
...    select_from(addresses.join(max_ids, addresses.c.id==max_ids.c.maxid)).\
...    where(
...    ~exists([1], and_(
...        addresses.c.user_id==aalias.c.user_id,
...        aalias.c.email_address.endswith('@blooger.biz')))
...    )
... )

With our statement above, we can drop it into the select_from() method on Query, which is a little bit like from_statement() except the Query will be ready to manipulate the incoming expression as though it were the primary mapped table:

>>> sess = create_session()
>>> sess.query(Address).select_from(aview)

When the above query fetches rows, the "row adaption" we discussed in the last section is applied to each incoming row, mapping the aview selectable back to the addresses table. This way, our Address mapper, which still only knows about the addresses table, can target columns against the addresses table when they are in fact adapted from columns targeted against the aliased aview selectable. In a concrete sense it means that result columns which are labeled (or if we were doing positional column targeting, positioned) corresponding to Column objects present in the aview construct can referenced with Column objects from the addresses table. When joins and eager loads are added on to this query, its very likely that the same row will be translated using many row adapters at result time, often translating different columns into the same underlying table, as is the case in our nodes example.

Clause Adaption

Clause adaption builds upon the idea of corresponding_column() to replace parts of an expression which "correspond" to another expression to that of the other expression. Originally this operation supported columns only and only worked with simplistic column-oriented expressions, but in recent months it's been expanded to support the replacement of pretty much any kind of expression, and has moved from an outerlying edge case into a core concept which provides the ability to query from "polymorphic" selectables (which select for several different mappers encapsulated in a subquery), to create aliased joins, as well as to allow joins to or from any arbitrary selectable; all generated strictly on the known relationships between the "underlying" tables.

Using our nodes example, we can apply filtering criterion constructed from the nodes table object and apply it towards the aliases of the nodes table. Such as, to load all nodes who have a grandchild containing the data field "crackers"; the desired SQL is:

SELECT nodes.node_id, nodes.parent_id, nodes.data
FROM nodes JOIN nodes as children ON nodes.node_id=children.parent_id
JOIN nodes as grandchildren ON children.node_id=grandchildren.parent_id
WHERE grandchildren.data="crackers"

If we were given the SQL construct:

>>> children = nodes.alias('children')
>>> grandchildren = nodes.alias('grandchildren')
>>> n = nodes.select().\
...     select_from(nodes.join(children, nodes.c.node_id==children.c.parent_id).\
...     join(grandchildren, children.c.node_id==grandchildren.c.parent_id))

we can apply the criterion nodes.c.data=='crackers', constructed without knowledge of the grandchildren alias, to that alias using adaption:

>>> from sqlalchemy.sql.util import ClauseAdapter
>>> criterion = nodes.c.data=='crackers'

>>> print criterion
nodes.data = :nodes_data

>>> adapted = ClauseAdapter(grandchildren).traverse(criterion)
>>> print adapted
grandchildren.data = :nodes_data

>>> print n.where(adapted)
SELECT nodes.node_id, nodes.parent_id, nodes.data
FROM nodes JOIN nodes AS children ON nodes.node_id = children.parent_id
JOIN nodes AS grandchildren ON children.node_id = grandchildren.parent_id
WHERE grandchildren.data = ?

Above, the ClauseAdapter object is created against the grandchildren alias. When it's given a clause to traverse(), it produces a copy of that clause with all elements that "correspond" to grandchildren replaced with the corresponding element, in this case the nodes.data column is replaced with grandchildren.data.

The above process is the same thing that happens automatically if you had a mapping setup for nodes, and used aliased joins as follows:

session.query(Node).join('children', 'children', aliased=True).\
   filter(Node.data=='crackers')

Let's take a look at how adaption applies to our users and addresses example. Those familiar with SQLAlchemy relations know that the "join condition" between the users and addresses tables is users.c.id==addresses.c.user_id. When we work with these mappers, anytime SA joins between a User and Address entity, that join condition is all it knows about how these two tables connect. Since SA never wants to assume things fit into an artificially constrained pattern, we can't "hardcode" to the fact that its really just a primary key/foreign key pair, as it can just as easily be a more complicated user-defined expression.

Clause adaption comes in when we start issuing joins between selectables which are derived from the base mapped tables. Suppose that when we execute the big addresses query and get back Address objects, we also want to get the User which maps to each address. We can of course construct this query manually, but we can also join() on the user relation as always, and clause adaption will take care of the fact that we are selecting from a big UNION and not the plain addresses table. Using compile() on Query to see the generated SQL reveals:

>>> print sess.query(Address).select_from(aview).join('user').add_entity(User).compile()
#!sql
SELECT
    anon_1.id AS anon_1_id, anon_1.email_address AS anon_1_email_address, anon_1.user_id AS anon_1_user_id,
    usertable.id AS usertable_id, usertable.name AS usertable_name
FROM
    (SELECT addresses.id AS id, addresses.email_address AS email_address, addresses.user_id AS user_id
    FROM addresses
    WHERE addresses.email_address LIKE ?

    UNION ALL

    SELECT addresses.id AS id, addresses.email_address AS email_address, addresses.user_id AS user_id
    FROM addresses JOIN
        (SELECT max(addresses.id) AS maxid FROM addresses GROUP BY addresses.user_id) AS max_ids ON addresses.id = max_ids.maxid
    WHERE NOT (EXISTS (
        SELECT 1 FROM addresses AS addresses_1 WHERE addresses.user_id = addresses_1.user_id AND addresses_1.email_address LIKE ?
    ))) AS anon_1
JOIN usertable ON usertable.id = anon_1.user_id ORDER BY anon_1.oid

The important part above is the join condition at the bottom; the join('user') call which would normally produce addresses JOIN usertable ON usertable.id = addresses.user_id was adapted against the aview selectable to produce the ON clause of usertable.id = anon_1.user_id; additionally the ORDER BY, normally ORDER BY addresses.oid was aliased to be ORDER BY anon_1.oid.

Aliasing like this happens to a more significant degree when we combine eager loading with LIMIT/OFFSET, where the LIMITED query must be wrapped in a selectable which joins against the child table or tables without the limit applied:

>>> print sess.query(Address).options(eagerload_all('user.addresses')).limit(3).compile()
#!sql
SELECT
    anon_1.addresses_id AS anon_1_addresses_id,
    anon_1.addresses_email_address AS anon_1_addresses_email_address,
    anon_1.addresses_user_id AS anon_1_addresses_user_id,
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    usertable_1.id AS usertable_1_id,
    usertable_1.name AS usertable_1_name
FROM (
    SELECT
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id,
        addresses.oid AS addresses_oid
    FROM addresses ORDER BY addresses.oid LIMIT 3 OFFSET 0
    ) AS anon_1
    LEFT OUTER JOIN usertable AS usertable_1 ON usertable_1.id = anon_1.addresses_user_id
    LEFT OUTER JOIN addresses AS addresses_1 ON usertable_1.id = addresses_1.user_id
    ORDER BY anon_1.oid, usertable_1.oid, addresses_1.oid

To formulate the above query, Query applied these adaptions:

• The left side of "usertable.id = addresses.user_id" was aliased to become "usertable_1.id = addresses.user_id", to join from the primary table to the users table.

• The "usertable.id = addresses.user_id" was aliased in a separate instance to become "usertable_1.id = addresses_1.user_id", to join from the eagerly loaded users to the eagerly loaded child addresses.

• and:

  addresses
    LEFT OUTER JOIN usertable AS usertable_1 ON usertable_1.id = addresses.user_id
    LEFT OUTER JOIN addresses AS addresses_1 ON usertable_1.id = addresses_1.user_id
  

  became (note the full text of anon_1, our LIMITed subquery, in place of addresses):

  (SELECT
  addresses.id AS addresses_id,
  addresses.email_address AS addresses_email_address,
  addresses.user_id AS addresses_user_id,
  addresses.oid AS addresses_oid
  FROM addresses ORDER BY addresses.oid LIMIT 3 OFFSET 0) AS anon_1
    LEFT OUTER JOIN usertable AS usertable_1 ON usertable_1.id = anon_1.addresses_user_id
    LEFT OUTER JOIN addresses AS addresses_1 ON usertable_1.id = addresses_1.user_id
  

  the final adaption adapted the right side of usertable_1.id=addresses.user_id as well as the left side of the larger join.

Column Reduction

Column reduction means that a collection of columns, such as in the columns clause of a SELECT statement, are reduced to the smallest number of meaningful columns. Meaningful here means that no column in the collection has a foreign key relationship to any other column in the collection, nor does the selectable have any WHERE criterion that would relate the two columns to be always the same value. This function is used by the Join construct as well as the ORM to construct the most minimal set of primary key columns for an expression.

Consider the example of "people", "engineers" and "managers". This is an "inheriting" configuration where "engineers" and "managers" both join to the common information in the "people" table:

>>> people = Table('people', meta,
...    Column('person_id', Integer, primary_key=True),
...    Column('name', String(50)),
...    Column('type', String(30)))

>>> engineers = Table('engineers', meta,
...   Column('engineer_id', Integer, ForeignKey('people.person_id'), primary_key=True),
...   Column('engineer_name', String(50)),
...   Column('primary_language', String(50)),
...  )

>>> managers = Table('managers', meta,
...   Column('manager_id', Integer, ForeignKey('people.person_id'), primary_key=True),
...   Column('manager_name', String(50))
...   )

A join which selects all rows from all tables outerjoins both engineers and managers to people:

>>> all_people = people.outerjoin(engineers).outerjoin(managers)

If we select from this join, we get:

>>> print all_people.select()
#!sql
SELECT
    people.person_id, people.name, people.type,
    engineers.engineer_id, engineers.engineer_name, engineers.primary_language,
    managers.manager_id, managers.manager_name
FROM people
    LEFT OUTER JOIN engineers ON people.person_id = engineers.engineer_id
    LEFT OUTER JOIN managers ON people.person_id = managers.manager_id

What's the most minimal primary key we can determine from the above join ? Rolling up all primary key columns is a little too naive:

>>> print [str(c) for c in all_people.c if c.primary_key]
['people.person_id', 'engineers.engineer_id', 'managers.manager_id']

Because engineer_id and manager_id are always going to have the same value as person_id (or be NULL). The difference here determines whether we'd need to say query.get([2,None,2]) to load person number 2, or just query.get([2]) (note that we don't account for the idea that both engineers and managers might point to the same person_id here, because we are specifically talking about a "joined table inheritance" pattern which does not support "multiple" inheritance).

The reduce_columns() function steps through and gives us just what we want:

>>> from sqlalchemy.sql.util import reduce_columns
>>> print [str(c) for c in reduce_columns(
...    [col for col in all_people.c if col.primary_key])]
['people.person_id']

reduce_columns() can also do its work given a set of criterion or clauses to use:

>>> s = select([engineers, managers]).\
...   where(engineers.c.engineer_name==managers.c.manager_name)
>>> print [str(c) for c in reduce_columns(list(s.c), s)]
['engineer_id', 'engineer_name', 'primary_language', 'manager_id']

Above, the manager_name column is removed since the WHERE criterion of the select states that they're equivalent.

There's a bunch of other functions, but these three took the longest time to get right (and clause adaption, which has improved massively, still has some ambiguous cases that need to be refined). So in future posts which talk about "adapting the join" or "translating rows", now you know what I mean.