Thread: Re: [SQLObject] order by sum and group by (Page 2)
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sean M. <se...@mo...> - 2006-08-04 18:45:14
|
It should definitely be read-only. There would be no meaning in trying to write a count of something. - Sean Oleg Broytmann wrote: > On Fri, Aug 04, 2006 at 10:10:29AM -0500, Rick Flosi wrote: >> I think it might not exist b/c it isn't very SQLObjecty. >> You'd be returning an object with a new field in it, SUM(), which isn't >> part of the SQLObject and probably a subset of the SQLObject fields as >> well. This isn't very OO which is what I think SQLObject was designed for. > > Sean have promised to try to produce a patch, but I am sure it would be > very hard to create a proper sqlobjectish design for this excessive column. > Should the designer add it to sqlmeta.columns? should it be a read-only > property? > > Oleg. |
From: Luke O. <lu...@me...> - 2006-08-04 22:24:44
|
Quoting Rick Flosi <rf...@im...>: > I think it might not exist b/c it isn't very SQLObjecty. > You'd be returning an object with a new field in it, SUM(), which isn't > part of the SQLObject and probably a subset of the SQLObject fields as > well. This isn't very OO which is what I think SQLObject was designed for. I think there are two possible scopes for this that could make me comfortable with it in the context of SQLObject's design. (There may be other acceptable designs that I'm not thinking of, of course, these are just my thoughts. :) ) 1. Adding elements to the column specification of a .select, for use in the query but not accessible in the returned objects. This seems like a relatively small-scale change, and at least allows these order-by-sum queries to be executed. Sean McBride's proposed .select syntax sounds good, but I'm not a fan of the 'extra' dictionary access on instances: due to instance caching, these extra fields would not necessarily be limited to the instances returned from the SelectResult, for one. 2. Create column-like objects that encapsulate derived values into a declarative form. These could then be used as .q variables in .select. Pseudo-example: class Salesperson(SQLObject): name = StringCol() sales = MultipleJoin('Sale') totalAmount = DerivedJoinSum('sales', 'amount') class Sale(SQLObject): salesperson = ForeignKey('Salesperson') amount = DecimalCol() Salesperson.select(orderBy=Salesperson.q.totalAmount) These columns would have no createSQL, would normally be retrieved separately from the intial column query, and would require some (serious?) modification to SelectResults/SQLBuilder to a) put the joined table in tables and b) alter the column spec based on their explicit inclusion in the Select and c) potentially make other injections to the select such as the implicit groupBy in this example. I'd be a big fan of #2, as I tend to use derived _get_ accessors heavily and there are some common patterns that I'd prefer to a) be declarative and b) to use in queries as in this example. I can provide further examples after the weekend, and if people are interested I would be willing to tackle initial implementation next week. - Luke |
From: Oleg B. <ph...@ma...> - 2006-08-05 17:57:39
|
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. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ma...> - 2006-08-06 10:57:45
|
A few comments about style... On Sat, Aug 05, 2006 at 07:39:11PM -0400, Sean McBride wrote: > + extraColsList = ops.get('extraColumns',[]) Don't hesitate to add an additional space. We do not worry too much about the size of the source. ;) BTW, wouldn't it better to use () instead of creating a new list every time? + extraColsList = ops.get('extraColumns', ()) > + gb = ops['groupBy'] gb? KGB? Don't be shy to write a longer names. ;) + groupBy = ops['groupBy'] + extraColumns = self.select.ops.get('extraColumns', ()) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Rick F. <rf...@im...> - 2006-08-07 16:00:45
|
I was thinking about this and database views came to mind. Could we handle this problem by creating a database view and using that object (readonly)? Something like (rough syntax): class MyView(SQLObject): class sqlmeta: _cacheValues = False _readOnly = True createSQL = """CREATE VIEW SELECT mytable.id, SUM(mytable.number) FROM mytable""" id = ForeignKey('MyTable') sum = FloatCol() What do you think of this idea? -- Rick On Sun, 6 Aug 2006, Sean McBride wrote: > Thanks Oleg. Yeah, I'll try to be more consistent on the names and more > "spacey" as well. In general I was trying to follow the style guide, > kinda... > > Also, I didn't realize that () was significantly cheaper than [], but I > suppose that's probably the case. Good point. > > I'll continue to work on it. > > - Sean > > Oleg Broytmann wrote: >> A few comments about style... >> >> On Sat, Aug 05, 2006 at 07:39:11PM -0400, Sean McBride wrote: >>> + extraColsList = ops.get('extraColumns',[]) >> >> Don't hesitate to add an additional space. We do not worry too much >> about the size of the source. ;) BTW, wouldn't it better to use () instead >> of creating a new list every time? >> >> + extraColsList = ops.get('extraColumns', ()) >> >>> + gb = ops['groupBy'] >> >> gb? KGB? Don't be shy to write a longer names. ;) >> >> + groupBy = ops['groupBy'] >> >> + extraColumns = self.select.ops.get('extraColumns', ()) >> >> Oleg. > > > ------------------------------------------------------------------------- > 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 > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Sean M. <se...@mo...> - 2006-08-07 18:17:58
|
Unfortunately this still doesn't solve the main thing I was trying to solve, which was an interface to groupBy select queries on regular SQLObjects. I suppose I could just create a custom view every time I wanted to do one of these queries and then write the SQL out by hand, but I'd rather just have a nice hook-in for it on the regular select method in a SQLObjecty manner. It's more convenient in 99% of my use cases. - Sean Rick Flosi wrote: > I was thinking about this and database views came to mind. > > Could we handle this problem by creating a database view and using that > object (readonly)? > > Something like (rough syntax): > > class MyView(SQLObject): > class sqlmeta: > _cacheValues = False > _readOnly = True > createSQL = """CREATE VIEW > SELECT mytable.id, SUM(mytable.number) FROM mytable""" > id = ForeignKey('MyTable') > sum = FloatCol() > > > What do you think of this idea? > > > -- > Rick > > On Sun, 6 Aug 2006, Sean McBride wrote: > >> Thanks Oleg. Yeah, I'll try to be more consistent on the names and more >> "spacey" as well. In general I was trying to follow the style guide, >> kinda... >> >> Also, I didn't realize that () was significantly cheaper than [], but I >> suppose that's probably the case. Good point. >> >> I'll continue to work on it. >> >> - Sean >> >> Oleg Broytmann wrote: >>> A few comments about style... >>> >>> On Sat, Aug 05, 2006 at 07:39:11PM -0400, Sean McBride wrote: >>>> + extraColsList = ops.get('extraColumns',[]) >>> Don't hesitate to add an additional space. We do not worry too much >>> about the size of the source. ;) BTW, wouldn't it better to use () instead >>> of creating a new list every time? >>> >>> + extraColsList = ops.get('extraColumns', ()) >>> >>>> + gb = ops['groupBy'] >>> gb? KGB? Don't be shy to write a longer names. ;) >>> >>> + groupBy = ops['groupBy'] >>> >>> + extraColumns = self.select.ops.get('extraColumns', ()) >>> >>> Oleg. >> >> ------------------------------------------------------------------------- >> 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 >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss >> > > ------------------------------------------------------------------------- > Using Tomcat but need to do more? Need to support web services, security? > Get stuff done quickly with pre-integrated technology to make your job easier > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 |