I am using mysql. I have a calss created using REflection >>> LinkVote Table('news_linkvote',MetaData(),Column(u'id',MSInteger(length=11),primary_key=True,nullable=False),Column(u'link_id',MSInteger(length=11),ForeignKey('news_link.id'),nullable=False),Column(u'user_id',MSInteger(length=11),ForeignKey('auth_user.id'),nullable=False),Column(u'direction',MSSmallInteger(length=1),nullable=False),Column(u'created_on',MSDateTime(timezone=False),nullable=False),schema=None)
I want to get a query like SELECT main_link.link_id, peer.link_id, count( peer.user_id ) / ( SELECT COUNT( countr.user_id ) FROM news_linkvote countr WHERE countr.link_id = peer.link_id ) corr FROM news_linkvote peer, news_linkvote main_link WHERE main_link.link_id =149 AND peer.user_id = main_link.user_id AND peer.direction = main_link.direction GROUP BY peer.link_id ORDER BY corr DESC LIMIT 0 , 30 So I do >>> main_link = LinkVote.alias('main_link') >>> peer = LinkVote.alias('peer') >>> countr = LinkVote.alias('countr') >>> conn.execute(select([main_link.c.link_id, peer.c.link_id, >>> func.count(peer.c.user_id)/select([func.count(countr.c.id)], >>> countr.c.link_id == peer.c.link_id, correlate = peer).scalar()], >>> and_(peer.c.user_id == main_link.c.user_id, peer.c.direction == >>> main_link.c.direction, main_link.c.link_id == 149), group_by = >>> peer.c.link_id, limit = 30)) And the query which I get is SELECT main_link.link_id, peer.link_id, count( peer.user_id ) / ( SELECT count( countr.id ) FROM news_linkvote AS countr, news_linkvote AS peer WHERE countr.link_id = peer.link_id ) FROM news_linkvote AS main_link, news_linkvote AS peer WHERE peer.user_id = main_link.user_id AND peer.direction = main_link.direction AND main_link.link_id =149 GROUP BY peer.link_id LIMIT 30 Notice that while I want the subquery to join with the outer peer table, it joins with a newly created inner peer table. I also tried, >>> conn.execute(select([main_link.c.link_id, peer.c.link_id, >>> func.count(peer.c.user_id)/select([func.count(countr.c.id)], >>> countr.c.link_id == peer.c.link_id, correlate = peer).scalar(), >>> correlate=peer], and_(peer.c.user_id == main_link.c.user_id, >>> peer.c.direction == main_link.c.direction, main_link.c.link_id == 149), >>> group_by = peer.c.link_id, limit = 30)) But this too gives me the same query. How can I get the firsts query? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---