Hi,
I was after some opinions on the following use of SA.
1. Is there any problems using SA in this way ?
2. Is there better ways of achieving this ?
My Mapper
db.mapper(Asset, db.asset_table,
properties = {
'location': relation(Location, lazy=False),
'type': relation(AssetType, lazy=False)
}
)
My SA query:
ast = db.asset_table.c
loc = db.location_table.c
criteria_list = (
(ast.branch_id, branch_id),
(loc.code, location_code),
(ast.serial_no, serial_no),
(ast.asset_no, asset_no),
(ast.model_no, model_no),
)
criteria = and_(*[col == value for col, value in criteria_list if
value is not None])
s = outerjoin(db.asset_table, db.location_table)
s = s.outerjoin(db.asset_type_table)
count = select([func.count(ast.id)], criteria).execute().fetchone()[0]
if order_by is None:
order_by = [text('''
COALESCE(CAST(SUBSTRING(%s FROM '([0-9]{1,10})') AS
INTEGER), 0),
lower(%s)
''' % (loc.code, loc.code))]
s = s.select(criteria, use_labels=True, limit=limit, offset=offset,
order_by=order_by)
query = db.query(model.Asset).options(contains_eager('location'),
contains_eager('type'))
r = query.instances(s.execute())
return r, count
The SQL generated from this is exactly what I want.
I have been trying to achieve the same thing using the query() interface
but I can't seem to control the order by clause properly (it is
generated inside an inner query when I have eagerload relations, and
therefore doesn't sort the resulting resultset).
Thanks
Huy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
