So first, a query:
SELECT process.process_id, op1.value_int, op2.value_string, op3.value_string
INNER JOIN route ON ( route.route_id = process.route_id )
LEFT OUTER JOIN obj_property op1
ON ( op1.obj_id = route.route_id AND
op1.namespace_prefix = 'http://www.opengroupware.us/oie' AND
op1.value_key = 'expireDays' )
LEFT OUTER JOIN obj_property op2
ON ( op2.obj_id = route.route_id AND
op2.namespace_prefix = 'http://www.opengroupware.us/oie' AND
op2.value_key = 'preserveAfterCompletion' )
LEFT OUTER JOIN obj_property op3
ON ( op3.obj_id = route.route_id AND
op3.namespace_prefix = 'http://www.opengroupware.us/oie' AND
op3.value_key = 'archiveAfterExpiration' )
WHERE process.db_status != 'archived'
AND process.state IN ( 'C', 'F', 'Z' )
AND process.status != 'archived';
Text 1: A sophisticated query that exploits the power of the PostgreSQL database engine.
This query returns the process_id value from the process table and then some values from multiple records from the table obj_property - if those records exist - correlated via an intermediate table route. Complex, but also fast!
Aside:With the correct DDL when creating the database PostgreSQL also enforces the integrity of the relations, maintains usage statistics, and provides multiple levels of atomicity. I certainly wouldn't want to have to do all that myself.So how to model such a query in SQLAlchemy? First realize that the result is actually going to be better than what straight SQL / DBAPI would give us. Instead of some values the query will return real objects, the advantage of this is the amount of code eliminated by having to do things based on ids or primary keys. This example assumes that the ORM entities Process, Route, and ObjectProperty have already been described - there is lots of documentation about how to declare the relations between your tables and your objects using the declarative style.
The SQLAlchemy aliased method declares multiple references to ObjectProperty that can be used independently: op1, op2, and op3. The other advanced technique is to use the outerjoin method to relate the need for a LEFT OUTER join.from sqlalchemy.orm import aliased
from sqlalchemy import and_, or_
db = ctx.db_session()
op1 = aliased(ObjectProperty)
op2 = aliased(ObjectProperty)
op3 = aliased(ObjectProperty)
q = db.query( Process, op1, op2, op3 ).\
join( Route, Route.object_id == Process.route_id ).\
outerjoin( op1, and_( op1.parent_id == Route.object_id,
op1.name=='expireDays' ), ).\
outerjoin( op2, and_( op2.parent_id == Route.object_id,
op2.name=='preserveAfterCompletion' ), ).\
outerjoin( op3, and_( op3.parent_id == Route.object_id,
op3.name=='archiveAfterExpiration' ), ).\
filter( and_( Process.state.in_( [ 'C', 'F', 'Z' ] ),
Process.status != 'archived' ) )
Text 2: The same query as above, only expressed via the SQLAlchemy ORM. But instead of returning values it returns live objects.
The results of this query will be tuples of four elements; the first being a Process object and the second, third, and fourth will either be ObjectProperty objects if the concomitant outer join identified a record or None if no record matched the join. The lovely upside of this is that the query results can be processed using a straight forward for-each construct:
Personally I find the ORM code to be easier to visually parse than the native SQL. Especially if you need to build the query dynamically or modify it based on the applications needs - since q is an object additional filter and join conditions can continue to be added. Imagine trying to do that with straight SQL?for process, expire_days, preserve_after, archive_after in q.all():if expire_days:....Text 3: Iterate over the query results; the first step depends if the op1 is an object (a record matched the first outer join).
Another advantage to this method is that SQLAlchemy can adapt it's dialect to the specific back-end if, for example, you are stuck using a database other that PostgreSQL. Without such an adaptive layer using anything other than the most trivial queries becomes daunting do to slight but important differences in how various engines express joins and nested queries.q = q.filter(Process.owner_id == 10100)
q = q.limit(150)
Text 4: Add one more filter expression to the queries WHERE clause and limit the query to 150 results.