Thread: [SQLObject] order by sum and group by
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sean O'D. <se...@od...> - 2006-08-03 22:49:31
|
Hi There, Im trying to figure out a way to order a query by the sum of a colum in a MultipleJoin e.g (not the original code, its messed up by me trying so many possible solutions :) class Salesman(SQLObject): name = StringCol() sales = MultipleJoin(Sale) class Sale(SQLObject): salesman = ForeignKey('Salesman') amount = IntCol() Now supposing I want to query a list of all of the Salesmen, ordered by the Sum of the amounts of their sales? Will SQLObject let me do that? If I was writing plain ole SQL I would do select salesman.name, sum(sale.amount) from salesman, sale where salesman.id = sale.salesman_id group by salesman.id order by sum(sale) desc. I dug through the SQLObject documentation and order by only seems to take single column names, I found some references to group by on this mailing list, but could not get anything working :( Thanks for your help Sean |
From: Sean M. <se...@mo...> - 2006-08-04 00:57:55
|
Unfortunately, it's not implemented right now, and it seems that only Seans need it! See my post a little ways down this list about it. As it currently stands, someone like you or I is going to have to implement it and submit to the list before this kind of ordering is possible. (Believe me, I have about 5-8 queries right now that could really use it.) I'm considering trying my hand at adding it but won't have time for at least a few more weeks due to my current project. - Sean Sean O'Donnell wrote: > Hi There, > > Im trying to figure out a way to order a query by the sum of a colum in > a MultipleJoin > > e.g (not the original code, its messed up by me trying so many possible > solutions :) > > class Salesman(SQLObject): > name = StringCol() > sales = MultipleJoin(Sale) > > class Sale(SQLObject): > salesman = ForeignKey('Salesman') > amount = IntCol() > > > Now supposing I want to query a list of all of the Salesmen, ordered by > the Sum of the amounts of their sales? > Will SQLObject let me do that? > > If I was writing plain ole SQL I would do > > select salesman.name, sum(sale.amount) from salesman, sale where > salesman.id = sale.salesman_id group by salesman.id order by sum(sale) > desc. > > I dug through the SQLObject documentation and order by only seems to > take single column names, I found some references to group by on this > mailing list, but could not get anything working :( > > Thanks for your help > > Sean > > ------------------------------------------------------------------------- > 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 |
From: Sean O'D. <se...@od...> - 2006-08-04 02:07:59
|
Yip, us poor seans seem to be flat out of luck. Im off to a wedding for the weekend, when I get back I will see about getting my hands dirty. I've never looked at the SQLObject source, so it should prove to be an education. Thanks for getting back to me so quickly. Sean Sean McBride wrote: > Unfortunately, it's not implemented right now, and it seems that only > Seans need it! See my post a little ways down this list about it. As it > currently stands, someone like you or I is going to have to implement it > and submit to the list before this kind of ordering is possible. > (Believe me, I have about 5-8 queries right now that could really use it.) > > I'm considering trying my hand at adding it but won't have time for at > least a few more weeks due to my current project. > > - Sean > > Sean O'Donnell wrote: > >> Hi There, >> >> Im trying to figure out a way to order a query by the sum of a colum in >> a MultipleJoin >> >> e.g (not the original code, its messed up by me trying so many possible >> solutions :) >> >> class Salesman(SQLObject): >> name = StringCol() >> sales = MultipleJoin(Sale) >> >> class Sale(SQLObject): >> salesman = ForeignKey('Salesman') >> amount = IntCol() >> >> >> Now supposing I want to query a list of all of the Salesmen, ordered by >> the Sum of the amounts of their sales? >> Will SQLObject let me do that? >> >> If I was writing plain ole SQL I would do >> >> select salesman.name, sum(sale.amount) from salesman, sale where >> salesman.id = sale.salesman_id group by salesman.id order by sum(sale) >> desc. >> >> I dug through the SQLObject documentation and order by only seems to >> take single column names, I found some references to group by on this >> mailing list, but could not get anything working :( >> >> Thanks for your help >> >> Sean >> >> ------------------------------------------------------------------------- >> 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 >> > > > ------------------------------------------------------------------------- > 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: Rick F. <rf...@im...> - 2006-08-04 15:10:37
|
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. On Fri, 4 Aug 2006, Sean O'Donnell wrote: > Yip, us poor seans seem to be flat out of luck. Im off to a wedding for > the weekend, when I get back > I will see about getting my hands dirty. I've never looked at the > SQLObject source, so it should prove > to be an education. > > Thanks for getting back to me so quickly. > > Sean > > Sean McBride wrote: >> Unfortunately, it's not implemented right now, and it seems that only >> Seans need it! See my post a little ways down this list about it. As it >> currently stands, someone like you or I is going to have to implement it >> and submit to the list before this kind of ordering is possible. >> (Believe me, I have about 5-8 queries right now that could really use it.) >> >> I'm considering trying my hand at adding it but won't have time for at >> least a few more weeks due to my current project. >> >> - Sean >> >> Sean O'Donnell wrote: >> >>> Hi There, >>> >>> Im trying to figure out a way to order a query by the sum of a colum in >>> a MultipleJoin >>> >>> e.g (not the original code, its messed up by me trying so many possible >>> solutions :) >>> >>> class Salesman(SQLObject): >>> name = StringCol() >>> sales = MultipleJoin(Sale) >>> >>> class Sale(SQLObject): >>> salesman = ForeignKey('Salesman') >>> amount = IntCol() >>> >>> >>> Now supposing I want to query a list of all of the Salesmen, ordered by >>> the Sum of the amounts of their sales? >>> Will SQLObject let me do that? >>> >>> If I was writing plain ole SQL I would do >>> >>> select salesman.name, sum(sale.amount) from salesman, sale where >>> salesman.id = sale.salesman_id group by salesman.id order by sum(sale) >>> desc. >>> >>> I dug through the SQLObject documentation and order by only seems to >>> take single column names, I found some references to group by on this >>> mailing list, but could not get anything working :( >>> >>> Thanks for your help >>> >>> Sean >>> >>> ------------------------------------------------------------------------- >>> 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 >>> >> >> >> ------------------------------------------------------------------------- >> 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 >> > > > ------------------------------------------------------------------------- > 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-04 18:39:48
|
I just don't really agree that this isn't very OOpy. Obviously it wouldn't be good to just mix the extra columns right into your existing SQLObject instances so they're different from your definition. However, either the SQLObject class or the SelectResults class could define some dict-like structure as a standard property for the "extra" columns returned that weren't used in constructing the actual SQLObject instances that pop out of the SelectResults instance. You'd have to use a "get" method to pull stuff out, and you wouldn't be guaranteed that the columns you were looking for would actually be there. But if you knew something about the query that produced the results, then you WOULD know where to look to find the extra stuff. Having OOPy style integration is great, but it's worthless if you have to dodge around the library completely or settle for really inefficient performance for some complex queries. I'd always prefer to find clever (and OO acceptable) ways to add functionality into an ORM library instead of just leaving functionality out. What do other people think? - Sean 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. > > On Fri, 4 Aug 2006, Sean O'Donnell wrote: > >> Yip, us poor seans seem to be flat out of luck. Im off to a wedding for >> the weekend, when I get back >> I will see about getting my hands dirty. I've never looked at the >> SQLObject source, so it should prove >> to be an education. >> >> Thanks for getting back to me so quickly. >> >> Sean >> >> Sean McBride wrote: >>> Unfortunately, it's not implemented right now, and it seems that only >>> Seans need it! See my post a little ways down this list about it. As it >>> currently stands, someone like you or I is going to have to implement it >>> and submit to the list before this kind of ordering is possible. >>> (Believe me, I have about 5-8 queries right now that could really use it.) >>> >>> I'm considering trying my hand at adding it but won't have time for at >>> least a few more weeks due to my current project. >>> >>> - Sean >>> >>> Sean O'Donnell wrote: >>> >>>> Hi There, >>>> >>>> Im trying to figure out a way to order a query by the sum of a colum in >>>> a MultipleJoin >>>> >>>> e.g (not the original code, its messed up by me trying so many possible >>>> solutions :) >>>> >>>> class Salesman(SQLObject): >>>> name = StringCol() >>>> sales = MultipleJoin(Sale) >>>> >>>> class Sale(SQLObject): >>>> salesman = ForeignKey('Salesman') >>>> amount = IntCol() >>>> >>>> >>>> Now supposing I want to query a list of all of the Salesmen, ordered by >>>> the Sum of the amounts of their sales? >>>> Will SQLObject let me do that? >>>> >>>> If I was writing plain ole SQL I would do >>>> >>>> select salesman.name, sum(sale.amount) from salesman, sale where >>>> salesman.id = sale.salesman_id group by salesman.id order by sum(sale) >>>> desc. >>>> >>>> I dug through the SQLObject documentation and order by only seems to >>>> take single column names, I found some references to group by on this >>>> mailing list, but could not get anything working :( >>>> >>>> Thanks for your help >>>> >>>> Sean >>>> >>>> ------------------------------------------------------------------------- >>>> 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 >>>> >>> >>> ------------------------------------------------------------------------- >>> 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 >>> >> >> ------------------------------------------------------------------------- >> 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 >> > > ------------------------------------------------------------------------- > 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 |
From: Rick F. <rf...@im...> - 2006-08-04 19:03:09
|
This sounds like a good idea. What do you see the syntax being for creating a query with SUM()? Will you have to define GroupBy() or will it default reasonably? How can you do a SUM() of a field and Group it by something and return the full object? SELECT id, number, SUM(number) FROM table GROUP BY id, number? I don't think that is what we want. -- Rick On Fri, 4 Aug 2006, Sean McBride wrote: > I just don't really agree that this isn't very OOpy. Obviously it > wouldn't be good to just mix the extra columns right into your existing > SQLObject instances so they're different from your definition. However, > either the SQLObject class or the SelectResults class could define some > dict-like structure as a standard property for the "extra" columns > returned that weren't used in constructing the actual SQLObject > instances that pop out of the SelectResults instance. You'd have to use > a "get" method to pull stuff out, and you wouldn't be guaranteed that > the columns you were looking for would actually be there. But if you > knew something about the query that produced the results, then you WOULD > know where to look to find the extra stuff. > > Having OOPy style integration is great, but it's worthless if you have > to dodge around the library completely or settle for really inefficient > performance for some complex queries. I'd always prefer to find clever > (and OO acceptable) ways to add functionality into an ORM library > instead of just leaving functionality out. > > What do other people think? > > - Sean > > 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. >> >> On Fri, 4 Aug 2006, Sean O'Donnell wrote: >> >>> Yip, us poor seans seem to be flat out of luck. Im off to a wedding for >>> the weekend, when I get back >>> I will see about getting my hands dirty. I've never looked at the >>> SQLObject source, so it should prove >>> to be an education. >>> >>> Thanks for getting back to me so quickly. >>> >>> Sean >>> >>> Sean McBride wrote: >>>> Unfortunately, it's not implemented right now, and it seems that only >>>> Seans need it! See my post a little ways down this list about it. As it >>>> currently stands, someone like you or I is going to have to implement it >>>> and submit to the list before this kind of ordering is possible. >>>> (Believe me, I have about 5-8 queries right now that could really use it.) >>>> >>>> I'm considering trying my hand at adding it but won't have time for at >>>> least a few more weeks due to my current project. >>>> >>>> - Sean >>>> >>>> Sean O'Donnell wrote: >>>> >>>>> Hi There, >>>>> >>>>> Im trying to figure out a way to order a query by the sum of a colum in >>>>> a MultipleJoin >>>>> >>>>> e.g (not the original code, its messed up by me trying so many possible >>>>> solutions :) >>>>> >>>>> class Salesman(SQLObject): >>>>> name = StringCol() >>>>> sales = MultipleJoin(Sale) >>>>> >>>>> class Sale(SQLObject): >>>>> salesman = ForeignKey('Salesman') >>>>> amount = IntCol() >>>>> >>>>> >>>>> Now supposing I want to query a list of all of the Salesmen, ordered by >>>>> the Sum of the amounts of their sales? >>>>> Will SQLObject let me do that? >>>>> >>>>> If I was writing plain ole SQL I would do >>>>> >>>>> select salesman.name, sum(sale.amount) from salesman, sale where >>>>> salesman.id = sale.salesman_id group by salesman.id order by sum(sale) >>>>> desc. >>>>> >>>>> I dug through the SQLObject documentation and order by only seems to >>>>> take single column names, I found some references to group by on this >>>>> mailing list, but could not get anything working :( >>>>> >>>>> Thanks for your help >>>>> >>>>> Sean >>>>> >>>>> ------------------------------------------------------------------------- >>>>> 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 >>>>> >>>> >>>> ------------------------------------------------------------------------- >>>> 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 >>>> >>> >>> ------------------------------------------------------------------------- >>> 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 >>> >> >> ------------------------------------------------------------------------- >> 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 > > > ------------------------------------------------------------------------- > 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: Oleg B. <ph...@ma...> - 2006-08-07 16:14:10
|
On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: > Could we handle this problem by creating a database view Do all the backends supported by SQLObject support VIEWs? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Dan P. <da...@ag...> - 2006-08-07 17:14:54
|
On Monday 07 August 2006 19:14, Oleg Broytmann wrote: > On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: > > Could we handle this problem by creating a database view > > Do all the backends supported by SQLObject support VIEWs? MySQL 4.x doesn't support them. There may be others. -- Dan |
From: Rick F. <rf...@im...> - 2006-08-07 17:10:59
|
On Mon, 7 Aug 2006, Oleg Broytmann wrote: > On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: >> Could we handle this problem by creating a database view > > Do all the backends supported by SQLObject support VIEWs? Postgres does. I don't know about any of the others off hand. Can anyone that knows comment? -- Rick |
From: Oleg B. <ph...@ph...> - 2006-08-07 17:16:13
|
On Mon, Aug 07, 2006 at 12:10:51PM -0500, Rick Flosi wrote: > On Mon, 7 Aug 2006, Oleg Broytmann wrote: > >On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: > >>Could we handle this problem by creating a database view > > > > Do all the backends supported by SQLObject support VIEWs? > > Postgres does. I don't know about any of the others off hand. MySQL (starting from 5.0.1), PostgreSQL and SQLite support VIEWs, in their own different ways. Don't know about other backends. 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 17:18:25
|
The query doesn't necessarily have to exist as a database view either. Maybe we can have: class sqlmeta: querySQL = """SELECT id, COUNT(number) FROM foo""" instead of using an actual database view. -- Rick On Mon, 7 Aug 2006, Rick Flosi wrote: > On Mon, 7 Aug 2006, Oleg Broytmann wrote: > >> On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: >>> Could we handle this problem by creating a database view >> >> Do all the backends supported by SQLObject support VIEWs? > > Postgres does. I don't know about any of the others off hand. > > Can anyone that knows comment? > > -- > Rick > |
From: Rick F. <rf...@im...> - 2006-08-07 17:23:18
|
And in this case you can still use createSQL to create the VIEW if your database supports it and querySQL can SELECT FROM your VIEW or just be an arbitray query. On Mon, 7 Aug 2006, Rick Flosi wrote: > The query doesn't necessarily have to exist as a database view either. > Maybe we can have: > class sqlmeta: > querySQL = """SELECT id, COUNT(number) FROM foo""" > instead of using an actual database view. > > -- > Rick > > On Mon, 7 Aug 2006, Rick Flosi wrote: > >> On Mon, 7 Aug 2006, Oleg Broytmann wrote: >> >>> On Mon, Aug 07, 2006 at 11:00:39AM -0500, Rick Flosi wrote: >>>> Could we handle this problem by creating a database view >>> >>> Do all the backends supported by SQLObject support VIEWs? >> >> Postgres does. I don't know about any of the others off hand. >> >> Can anyone that knows comment? >> >> -- >> Rick >> > > ------------------------------------------------------------------------- > 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 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Sean M. <se...@mo...> - 2006-08-04 20:47:50
|
Well, the SQLObject syntax would probably look something like this: Item.select(Item.q.id==table.collection_item.item_id, extraColumns = [func.COUNT(Item.q.id)], groupBy = Item.q.id, orderBy = [DESC(func.COUNT(Item.q.id)),Item.q.name]) The resulting SQL would then look something like this: SELECT item.*, COUNT(item.id) FROM item, collection_item WHERE item.id=collection_item.item_id GROUP BY item.id ORDER BY COUNT(item.id) DESC, item.name ASC; groupBy will default to the NoDefault thing and not go into SQL queries by default. This is consistent with normal SQL behavior. If groupBy is not present the accumulators will just operate over the entire result set (which is what happens when you do a COUNT(*) That's what I'm currently thinking anyway. - Sean Rick Flosi wrote: > This sounds like a good idea. > > What do you see the syntax being for creating a query with SUM()? > > Will you have to define GroupBy() or will it default reasonably? > > How can you do a SUM() of a field and Group it by something and return the > full object? > SELECT id, number, SUM(number) FROM table GROUP BY id, number? > I don't think that is what we want. > > -- > Rick > > On Fri, 4 Aug 2006, Sean McBride wrote: > >> I just don't really agree that this isn't very OOpy. Obviously it >> wouldn't be good to just mix the extra columns right into your existing >> SQLObject instances so they're different from your definition. However, >> either the SQLObject class or the SelectResults class could define some >> dict-like structure as a standard property for the "extra" columns >> returned that weren't used in constructing the actual SQLObject >> instances that pop out of the SelectResults instance. You'd have to use >> a "get" method to pull stuff out, and you wouldn't be guaranteed that >> the columns you were looking for would actually be there. But if you >> knew something about the query that produced the results, then you WOULD >> know where to look to find the extra stuff. >> >> Having OOPy style integration is great, but it's worthless if you have >> to dodge around the library completely or settle for really inefficient >> performance for some complex queries. I'd always prefer to find clever >> (and OO acceptable) ways to add functionality into an ORM library >> instead of just leaving functionality out. >> >> What do other people think? >> >> - Sean >> >> 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. >>> >>> On Fri, 4 Aug 2006, Sean O'Donnell wrote: >>> >>>> Yip, us poor seans seem to be flat out of luck. Im off to a wedding for >>>> the weekend, when I get back >>>> I will see about getting my hands dirty. I've never looked at the >>>> SQLObject source, so it should prove >>>> to be an education. >>>> >>>> Thanks for getting back to me so quickly. >>>> >>>> Sean >>>> >>>> Sean McBride wrote: >>>>> Unfortunately, it's not implemented right now, and it seems that only >>>>> Seans need it! See my post a little ways down this list about it. As it >>>>> currently stands, someone like you or I is going to have to implement it >>>>> and submit to the list before this kind of ordering is possible. >>>>> (Believe me, I have about 5-8 queries right now that could really use it.) >>>>> >>>>> I'm considering trying my hand at adding it but won't have time for at >>>>> least a few more weeks due to my current project. >>>>> >>>>> - Sean >>>>> >>>>> Sean O'Donnell wrote: >>>>> >>>>>> Hi There, >>>>>> >>>>>> Im trying to figure out a way to order a query by the sum of a colum in >>>>>> a MultipleJoin >>>>>> >>>>>> e.g (not the original code, its messed up by me trying so many possible >>>>>> solutions :) >>>>>> >>>>>> class Salesman(SQLObject): >>>>>> name = StringCol() >>>>>> sales = MultipleJoin(Sale) >>>>>> >>>>>> class Sale(SQLObject): >>>>>> salesman = ForeignKey('Salesman') >>>>>> amount = IntCol() >>>>>> >>>>>> >>>>>> Now supposing I want to query a list of all of the Salesmen, ordered by >>>>>> the Sum of the amounts of their sales? >>>>>> Will SQLObject let me do that? >>>>>> >>>>>> If I was writing plain ole SQL I would do >>>>>> >>>>>> select salesman.name, sum(sale.amount) from salesman, sale where >>>>>> salesman.id = sale.salesman_id group by salesman.id order by sum(sale) >>>>>> desc. >>>>>> >>>>>> I dug through the SQLObject documentation and order by only seems to >>>>>> take single column names, I found some references to group by on this >>>>>> mailing list, but could not get anything working :( >>>>>> >>>>>> Thanks for your help >>>>>> >>>>>> Sean >>>>>> >>>>>> ------------------------------------------------------------------------- >>>>>> 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 >>>>>> >>>>> ------------------------------------------------------------------------- >>>>> 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 >>>>> >>>> ------------------------------------------------------------------------- >>>> 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 >>>> >>> ------------------------------------------------------------------------- >>> 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 >> >> ------------------------------------------------------------------------- >> 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 >> > > ------------------------------------------------------------------------- > 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 |
From: Oleg B. <ph...@ma...> - 2006-08-05 10:59:51
|
On Fri, Aug 04, 2006 at 04:47:25PM -0400, Sean McBride wrote: > Item.select(Item.q.id==table.collection_item.item_id, > extraColumns = [func.COUNT(Item.q.id)], > groupBy = Item.q.id, > orderBy = [DESC(func.COUNT(Item.q.id)),Item.q.name]) You've forgotten a way to declare the extra columns in the Item class. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sean M. <se...@mo...> - 2006-08-04 22:17:01
|
So I'm digging through code and getting a feel for how things work with SelectResults and SQLObject objects in order to eventually add some groupBy and extra columns support to the SQLObject.select class method. I noticed that in queryForSelect method of the DBAPI class in dbconnection.py, there is code for building a SQL select statement up from scratch. Is there a reason why this doesn't just use sqlbuilder's support for building select statements? I assumed that's what would happen so that I'd just have to add a groupBy to that select statement creation, but DBAPI appears to build its own SQL code independently of sqlbuilder. Wouldn't it be better for it to use sqlbuilder, or is there some reason it doesn't (like it was written before sqlbuilder, perhaps?) - Sean |
From: Oleg B. <ph...@ma...> - 2006-08-05 11:01:48
|
On Fri, Aug 04, 2006 at 06:16:31PM -0400, Sean McBride wrote: > I noticed that in queryForSelect method of the DBAPI class in > dbconnection.py, there is code for building a SQL select statement up > from scratch. Is there a reason why this doesn't just use sqlbuilder's > support for building select statements? I assumed that's what would > happen so that I'd just have to add a groupBy to that select statement > creation, but DBAPI appears to build its own SQL code independently of > sqlbuilder. I always was surprised by this dichotomy, too. > Wouldn't it be better for it to use sqlbuilder I think it would. But moving DBAPI code to sqlbuilder would be a hard job now, when they are so different. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sean M. <se...@mo...> - 2006-08-04 23:22:56
|
Number 2 sounds decent to me too, but I can see a potential problem: A class that had a DerivedJoinSum on it (or another one of the derived accumulators) would ALWAYS have to do the more complicated select statement behind the scenes in order to get the data into the object instance. BUT you wouldn't always need to USE that data, so it'd really be sort of a waste. Perhaps those columns are only populated (and thus the more complex query with group by executed) if you use them in the orderBy clause? Otherwise they would raise an error if accessed? But this also seems not ideal... The problem is that the extra columns are really just a part of doing a special kind of select on the model, which is why I wanted to somehow explicitly associate them with the select instead of the model in general. I don't think it's THAT complicated to use the groupBy/orderBy syntax. Semantically, it's pretty obvious and straightforward, and it only does the grouping and selecting when you need it to. I think that's the advantage of putting it on the select. However, the question still remains how to get the extra values back with the model instances. I didn't think of the caching issue. If you're doing multiple groupBy selects that involve the same object, then caching will probably unify the two and you could have potential collisions on the extraColumns dict... Hrm The only other thing I can think of is handling with the iterator like this: results = Item.select(extraColumns={'count':func.COUNT(Item.q.id)}, groupBy=something, orderBy=somethingElse) for obj, extraColumns in results: print obj.id, obj.name, extraColumns['count'] Result: 1 Sam 15 2 Brad 16 etc... Would that make sense? I kinda like it... - Sean Luke Opperman wrote: > 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 > > ------------------------------------------------------------------------- > 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 |
From: Luke O. <lu...@me...> - 2006-08-05 02:43:42
|
Quoting Sean McBride <se...@mo...>: > Number 2 sounds decent to me too, but I can see a potential problem: A > class that had a DerivedJoinSum on it (or another one of the derived > accumulators) would ALWAYS have to do the more complicated select > statement behind the scenes in order to get the data into the object > instance. BUT you wouldn't always need to USE that data, so it'd really > be sort of a waste. Perhaps those columns are only populated (and thus > the more complex query with group by executed) if you use them in the > orderBy clause? Otherwise they would raise an error if accessed? But > this also seems not ideal... I had pictured the opposite, derived columns would not be included in the column spec unless explicitly used in a .select. Today i would write this derived column as: def _get_totalAmount(self): return self.sales.sum(self.sales.otherClass.q.amount) and the version when not retrieved through an explicit select would still be this. Actually, even the select version would need to do this unless we introduce a synchronization/clear mechanism for cacheValues on joins. Hmm. More later, i'm about to lose coverage up in the northwoods. :) |
From: Oleg B. <ph...@ma...> - 2006-08-05 11:03:54
|
On Fri, Aug 04, 2006 at 07:22:46PM -0400, Sean McBride wrote: > for obj, extraColumns in results: I hope you are not going to break all existing programs that iterate over SelectResults? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sean M. <se...@mo...> - 2006-08-05 17:45:05
|
No, it would only return the tuple if you had included the extraColumns argument in the select method call. Otherwise the behavior would be indentical to now, so all existing code would still work. What, you think I'm crazy? ;) Oleg Broytmann wrote: > On Fri, Aug 04, 2006 at 07:22:46PM -0400, Sean McBride wrote: >> for obj, extraColumns in results: > > I hope you are not going to break all existing programs that iterate > over SelectResults? > > Oleg. 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. - Sean Oleg Broytmann wrote: > On Fri, Aug 04, 2006 at 04:47:25PM -0400, Sean McBride wrote: >> Item.select(Item.q.id==table.collection_item.item_id, >> extraColumns = [func.COUNT(Item.q.id)], >> groupBy = Item.q.id, >> orderBy = [DESC(func.COUNT(Item.q.id)),Item.q.name]) > > You've forgotten a way to declare the extra columns in the Item class. > > Oleg. |
From: Oleg B. <ph...@ma...> - 2006-08-07 17:21:09
|
On Mon, Aug 07, 2006 at 12:18:23PM -0500, Rick Flosi wrote: > class sqlmeta: > querySQL = """SELECT id, COUNT(number) FROM foo""" What this query is about? What if I want different queries? Should I change sqlmeta every time? Even in multithreaded programs? 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 17:25:38
|
I would think that you would be creating a new object that is somehow useful. I don't expect you'd change the query. On Mon, 7 Aug 2006, Oleg Broytmann wrote: > On Mon, Aug 07, 2006 at 12:18:23PM -0500, Rick Flosi wrote: >> class sqlmeta: >> querySQL = """SELECT id, COUNT(number) FROM foo""" > > What this query is about? What if I want different queries? Should I > change sqlmeta every time? Even in multithreaded programs? > > Oleg. > |
From: Sean M. <se...@mo...> - 2006-08-05 23:39:27
Attachments:
diff.txt
|
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. |
From: Sean M. <se...@mo...> - 2006-08-06 18:52:55
|
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. |
From: Oleg B. <ph...@ma...> - 2006-08-04 15:26:38
|
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. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |