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
-~----------~----~----~----~------~----~------~--~---
