Re: Group by paramether in getSome()

On Saturday 25 June 2005 04:25, Jacob Smullyan wrote:

> > Every time I've to do a SELECT with GROUP BY statement I've to use the
> > DBI's execute() method... Perhaps an additional, optional paramether to
> > getSome() could be useful to others?
>
> You can use fetch() for this.  There isn't any reason to use
> execute(), I don't think, now that you have fetch().

fetch() is useful for joins, but for queries on a single table is not the 
best choice (actually); for example, on a single table it returns a list of 
tuples (each tuple contains only a dictionary), it should be a simple list 
of dictionaries...

> I suppose it would be possible to add a parameter, "group", to
> getSome(), since it is a sql keyword and couldn't be a column name.

Yes, I'm working on that and it seems to work  ;)
How can I submit my changes to you?

> But would it be that useful, given that the result set in getSome() is
> fixed and can't contain aggregates?

Oh, the result CAN contain aggregates, with a simple project() trick  ;)
You should know...

Oh, I was talking about project()... Here's a couple of "bugs" I've found:

* To read the value of an aggregate field I've to use the dictionary type 
access: instance["aggregate_alias"] instead of instance.aggregate_alias
Annoying, but I can live with this

* In last beta of PyDO the getUnique() method doesn't work on projected 
classes. Example:

class Friends(PyDO):
	connectionAlias = 'main_db'
	fields = ('username', 'friend', 'relationship')
	unique = (('username', 'friend'), )

	@classmethod
	def areFriends(cls, username, friend):
		return bool(cls.project("username", "friend").getUnique(username=username, 
friend=friend))

Friends.areFriends('a', 'b') works, but that would not:

Friends.project("username", "friend").areFriends('a', 'b')

Note that this code worked in the last beta release of PyDO, while in the 
latest it gives that error:

File "/home/faber/pydo/base.py", line 511, in getUnique
    where, values = cls._uniqueWhere(conn, fieldData)
File "/home/faber/pydo/base.py", line 489, in _uniqueWhere
    raise ValueError, 'No way to get unique row! %s %s' % \
ValueError: No way to get unique row! {'username': 'a', 'friend': 'b'} None

> Could you give an example of a query that you want to run with getSome()
> that has a group by clause? 

SELECT product_name, COUNT(product_name) AS customers_count
FROM sold_products
WHERE Product = 128
GROUP BY product_name
ORDER BY COUNT(product_name) DESC, product_name

-- 
Faber
http://www.faberbox.com/

Now listening: Pink Fairies - Right On, Fight On


-------------------------------------------------------
SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
from IBM. Find simple to follow Roadmaps, straightforward articles,
informative Webcasts and more! Get everything you need to get up to
speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click