I have a MySQL music database, and I wanted to display a graph of the top 10 artists played per hour in a given time period. The first query I tried was:
select([func.DATE_FORMAT(Play.stime, '%Y/%m/%d').label('day'), func.COUNT(Play.id).label('playcount')], and_(Play.stime > '2008/09/01', Artist.name.in_( select([Artist.name, func.COUNT(Play.id).label('playcount')], Play.stime > '2008/09/01', from_obj=[play_table.join(track_table).join(artist_table)], group_by=['name'], order_by=['playcount desc']).limit(10) )), from_obj=[play_table.join(track_table).join(artist_table)], group_by=['day'], order_by=['day']) This returns the following error from MySQL: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery" Some Googling led me to a workaround for this problem that involves moving the subquery out of the WHERE clause and into the FROM clause, and joining on the subquery results. I ended up with something like this: subquery = select([Artist.name, func.COUNT(Play.id).label('playcount')], Play.stime > '2008/09/01', from_obj=[play_table.join(track_table).join(artist_table)], group_by=['name'], order_by=['playcount desc']).limit(10); select([func.DATE_FORMAT(Play.stime, '%Y/%m/%d').label('day'), func.COUNT(Play.id).label('playcount')], Play.stime > '2008/09/01', from_obj=[play_table.join(track_table).join(artist_table).\ join(subquery) ], group_by=['day'], order_by=['day']).execute() This gives me the following SQLAlchemy error: ArgumentError: Can't find any foreign key relationships between 'Join object on Join object on play(26328912) and track(26327472)(32479856) and artist(26327824)' and '_FromGrouping object' My interpretation of this error is that SQLAlchemy is having trouble joining the other tables with the results of the subquery. So, I figured I'd provide an explicit join condition, but I don't know the right syntax to do it. I know the join() method takes an argument for the fields to join on, but how do I refer to the columns in the "subquery" table above? Thanks for any and all help. -Tony --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---