Re: [SQLObject] A SelectResults derived class to handle join queries
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Luke O. <lu...@me...> - 2007-08-03 21:10:05
|
As with the general questions that come up around .select() and joins, the underlying issue is the expectation that "instances of a SQLObject-derived class are returned". So in your work here, the main bit is creating a fake class that is "enough" like a SQLObject. Also in trunk (views.py) is an alternate approach I took, ViewSQLObject: """ A SQLObject class that derives all it's values from other SQLObject classes. Columns on subclasses should use SQLBuilder constructs for dbName, and sqlmeta should specify: * idName as a SQLBuilder construction * clause as SQLBuilder clause for specifying join conditions or other restrictions * table as an optional alternate name for the class alias See test_views.py for simple examples. """ In many ways it's no prettier a hack than your efforts, but here's what it would look like using it for the problem in the other active thread on this topic (Jim Steil): class RailCarWithStatus(ViewSQLObject): class sqlmeta: idName = RailCar.q.id clause = RailCar.q.id==RailStatus.q.railCarID carInitials = StringCol(dbName=RailCar.q.carInitials) # ... other RailCar columns to pull in status = StringCol(dbName=RailStatus.q.status) # ... other RailStatus columns to pull in and then you'd just do RailCarWithStatus.select(...) It's still rough, I'm not directly advocating its use yet (I don't believe it supports SQLBuilder JOIN syntaxes so outer joins are currently out, for instance, although it would be easy to add. Same with groupBy etc - as the api currently stands, presumably most of the optional arguments to Select would end up in sqlmeta to support these things). The motivation when writing it was to define aggregate columns to be retrieved with instance loading, so there's a lot of work in generating "safe" nested aliased select statements - it's been tested on PostgreSQL & SQLite as I recall. The clear alternative approach would be along your lines, emphasizing SQLBuilder/SelectResults and loosening the ties to SQLObjects so that it's easier to substitute other classes to be returned. In a lot of ways I like that direction, let me refresh my memory on the guts of this code and see what else I'd do with your approach. - Luke |