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