Ok, here's an example of the type of thing I'm trying to do: 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;
Assume that item is a model table and collection_item is a join table between items and collections. This statement will select all of the items that are in collections ordered by the number of collections they are in (from most to least). Since the statement is returning item.*, I should be able to get out a SelectResults instance full of SQLObject instances for the item model class. This way I can use all of the nice features of SQLObject instances (having all of my multiple and related join properties, updating, deleting, etc. I find myself wanting to make this kind of query all the time on my models, but not being able to. Finally, what to do with the extra columns that are returned? (In this example's case, the COUNT(item.id). Well, you could just throw it away. But, it MIGHT be nice to have access to them in some way (if I'm displaying a list of the items and want to show exactly how many collections each was in.) Maybe there could be an extra thing on the SelectResults iterator or maybe even on the SQLObject instances themselves? What do you think? Thanks for your reply. - Sean Oleg Broytmann wrote: > On Sun, Jul 30, 2006 at 04:56:54AM -0400, Sean McBride wrote: >> While using SQLObject, I find myself CONSTANTLY wanting to use Group By >> in selects for my SQLObject classes. I know that Oleg has mentioned >> before that Group By's don't make sense in select for a SQL object, but >> I think they do! As long as you are selecting all of the rows from the >> table that correspond to the given SQLObject, why should SQLObject care >> if you are using a Group By or not? It can still take those results and >> turn them into instances of your SQLObject class if you have selected >> all of the necessary rows. That's all it should care about. > > I do not understand this. Can you show a brief example? Please write a > SELECT with GROUP BY that selects some or all rows from a table. > >> The reason why I want Group By so badly is because there are some >> orderings that can only be accomplished by using them. Group By changes >> the behaviou of the accumulation functions that are used in the Order By >> statement, allowing you to do things like sort by the number of objects >> joined in a one-to-many or many-to-many relationship, or sort by the >> latest reply date in a forum. > > Use Select() from SQLBuilder. It has groupBy. > > 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 [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
