Ok, attached is a VERY rough patch of something that works (at least for
simple cases) and shows that it's possible to implement this way. The
patch is NOT tested and needs some nicing, error checking, etc.
I just wanted to get reactions and put this up there. It's not finished.
Basically, SQLObject.select method has two new optional arguments:
extraColumns and groupBy.
groupBy should be something like (tableName.colName) and extraColumns
should be a sequence of 2-tuples for name and column, like this:
[('collectionCount':func.COUNT(tableName.id))]
An example of using the new stuff:
--------------------------------------
class Item(SQLObject):
name = StringCol(length=255)
collection = ForeignKey('Collection')
class Collection(SQLObject):
name = StringCol(length=255)
items = MultipleJoin('Item')
items = Item.select(clause = (Item.q.collectionID == Collection.q.id),
extraColumns = [('collectionCount', func.COUNT(Item.q.id))],
groupBy = Item.q.id,
orderBy = [DESC(func.COUNT(Item.q.id)), Item.q.name])
for item, ec in items:
print "ITEM: %s COLLECTION COUNT: %s" % (item.name,
ec['collectionCount'])
----------------------------------------
Yeah, so, feedback anyone? I'm having a hard time figuring out how to
get the tests stuff set up so that I can write my one test file for this
feature...
- Sean
Oleg Broytmann wrote:
On Sat, Aug 05, 2006 at 01:44:55PM -0400, Sean McBride wrote:
What, you think I'm crazy? ;)
I very much hope you are! Because to dive deep into SQLObject code and
successfully write a major patch one must really be crazy! ;)
Actually, you don't need to declare the extra columns in the Item class,
since they're only around over the course of this select query. Include
the extraColumns argument on the select and the resulting SelectResults
lazy iterator would spit out (SQLObjectInstance, extraColumnsDict)
tuples instead of just SQLObject instances.
Well, that sounds pretty reasonable!
Oleg.
Index: sqlobject/dbconnection.py
===================================================================
--- sqlobject/dbconnection.py (revision 1846)
+++ sqlobject/dbconnection.py (working copy)
@@ -412,6 +412,11 @@
else:
columns = ", ".join(["%s.%s" % (cls.sqlmeta.table, col.dbName)
for col in cls.sqlmeta.columnList])
+ extraColsList = ops.get('extraColumns',[])
+ if extraColsList:
+ extraCols = ", ".join(["%s as %s" % (self.sqlrepr(col[1]),
col[0])
+ for col in extraColsList])
+ columns = ", ".join([columns, extraCols])
if columns:
q += "%s.%s, %s FROM %s" % \
(cls.sqlmeta.table, cls.sqlmeta.idName, columns,
@@ -469,6 +474,12 @@
q = self.sqlrepr(q)
ops = select.ops
+ if ops.get('groupBy'):
+ gb = ops['groupBy']
+ if type(gb) not in [type(''), type(u'')]:
+ gb = self.sqlrepr(gb)
+ q = "%s GROUP BY %s" % (q, gb)
+
def clauseList(lst, desc=False):
if type(lst) not in (type([]), type(())):
lst = [lst]
@@ -767,7 +778,14 @@
obj = self.select.sourceClass.get(result[0],
connection=self.dbconn)
return obj
else:
- obj = self.select.sourceClass.get(result[0],
selectResults=result[1:], connection=self.dbconn)
+ ec = self.select.ops.get('extraColumns',[])
+ end = len(result)-len(ec)
+ obj = self.select.sourceClass.get(result[0],
selectResults=result[1:end], connection=self.dbconn)
+ if ec:
+ extra = {}
+ for i in range(len(ec)):
+ extra[ec[i][0]] = result[i+end]
+ return (obj, extra)
return obj
def _cleanup(self):
Index: sqlobject/main.py
===================================================================
--- sqlobject/main.py (revision 1846)
+++ sqlobject/main.py (working copy)
@@ -1314,7 +1314,8 @@
orderBy=NoDefault, limit=None,
lazyColumns=False, reversed=False,
distinct=False, connection=None,
- join=None):
+ join=None, extraColumns=None,
+ groupBy=None):
return cls.SelectResultsClass(cls, clause,
clauseTables=clauseTables,
orderBy=orderBy,
@@ -1323,7 +1324,9 @@
reversed=reversed,
distinct=distinct,
connection=connection,
- join=join)
+ join=join,
+ extraColumns=extraColumns,
+ groupBy=groupBy)
select = classmethod(select)
def selectBy(cls, connection=None, **kw):
Index: sqlobject/sresults.py
===================================================================
--- sqlobject/sresults.py (revision 1846)
+++ sqlobject/sresults.py (working copy)
@@ -31,6 +31,8 @@
self.ops['dbOrderBy'] = orderBy
if ops.has_key('connection') and ops['connection'] is None:
del ops['connection']
+ if ops.has_key('extraColumns') and ops['extraColumns'] is None:
+ del ops['extraColumns']
def __repr__(self):
return "<%s at %x>" % (self.__class__.__name__, id(self))
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
