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
>>>>> sqlobject-discuss@lists.sourceforge.net
>>>>> 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
>>>> sqlobject-discuss@lists.sourceforge.net
>>>> 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
>> sqlobject-discuss@lists.sourceforge.net
>> 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
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
