[sqlalchemy] Problem with correlated subqueries
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
-~--~~~~--~~--~--~---
[sqlalchemy] Use of new_instance() in the user-defined-state branch?
I just noticed that in the latest version of the branch, there's a new_instance() call that is using a class' __new__ method in order to create a new instance, rather than using 'class()'. What I'd like to find out is how to get around this, because Trellis objects will not be properly initialized unless the 'class()' is called, with any initialization taking place inside __new__ and/or __init__. Trellis doesn't override __new__ or __init__, and doesn't care what they do. But the creation of an instance *must* be wrapped by the class' __call__ (i.e. class()), as there is a try/finally involved that must execute. Any thoughts on how this might be refactored? What is new_instance() used for? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
Phillip J. Eby wrote: I just noticed that in the latest version of the branch, there's a new_instance() call that is using a class' __new__ method in order to create a new instance, rather than using 'class()'. What I'd like to find out is how to get around this, because Trellis objects will not be properly initialized unless the 'class()' is called, with any initialization taking place inside __new__ and/or __init__. Trellis doesn't override __new__ or __init__, and doesn't care what they do. But the creation of an instance *must* be wrapped by the class' __call__ (i.e. class()), as there is a try/finally involved that must execute. Any thoughts on how this might be refactored? What is new_instance() used for? new_instance creates an instance without invoking __init__. The ORM uses it to recreate instances when loading from the database. new_instance can be added to InstrumentationManager as an extension method... The ORM doesn't care how empty instances are manufactured so long as they can be created without initialization arguments, e.g. a no-arg constructor. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
On Mar 27, 2008, at 2:51 PM, Phillip J. Eby wrote: I just noticed that in the latest version of the branch, there's a new_instance() call that is using a class' __new__ method in order to create a new instance, rather than using 'class()'. What I'd like to find out is how to get around this, because Trellis objects will not be properly initialized unless the 'class()' is called, with any initialization taking place inside __new__ and/or __init__. Trellis doesn't override __new__ or __init__, and doesn't care what they do. But the creation of an instance *must* be wrapped by the class' __call__ (i.e. class()), as there is a try/finally involved that must execute. Any thoughts on how this might be refactored? What is new_instance() used for? also, rev4361 has broken some tests. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
On Mar 27, 2008, at 3:11 PM, Michael Bayer wrote: also, rev4361 has broken some tests. rev 4362 fixes that particular issue. I know that the sweep function there is sensitive in other cases, such as ticket #882, where if not done properly it can also trip up zope.interface...so it might be good to ensure that we aren't breaking that ticket again. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
Phillip J. Eby wrote: At 12:02 PM 3/27/2008 -0700, jason kirtland wrote: Phillip J. Eby wrote: I just noticed that in the latest version of the branch, there's a new_instance() call that is using a class' __new__ method in order to create a new instance, rather than using 'class()'. What I'd like to find out is how to get around this, because Trellis objects will not be properly initialized unless the 'class()' is called, with any initialization taking place inside __new__ and/or __init__. Trellis doesn't override __new__ or __init__, and doesn't care what they do. But the creation of an instance *must* be wrapped by the class' __call__ (i.e. class()), as there is a try/finally involved that must execute. Any thoughts on how this might be refactored? What is new_instance() used for? new_instance creates an instance without invoking __init__. The ORM uses it to recreate instances when loading from the database. new_instance can be added to InstrumentationManager as an extension method... The ORM doesn't care how empty instances are manufactured so long as they can be created without initialization arguments, e.g. a no-arg constructor. Does that mean that no attributes must be set from new_instance(), either? You should be able to set whatever you like there. On a separate note, I noticed that the class manager machinery allowed one to just directly subclass ClassManager instead of making an InstrumentationManager. Was that intentional? I preserved this behavior when I corrected the staticmethod failure problem, but the tests don't appear to test for that. Yes, that's an intentional capability. I'm actively refactoring the entire mechanism to work in the MapperExtension-driven instrumentation election that's desired for the Zope security proxy integration; covering tests will be committed shortly as the whole starts to take a usable shape. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
At 02:26 PM 3/27/2008 -0700, jason kirtland wrote: new_instance creates an instance without invoking __init__. The ORM uses it to recreate instances when loading from the database. new_instance can be added to InstrumentationManager as an extension method... The ORM doesn't care how empty instances are manufactured so long as they can be created without initialization arguments, e.g. a no-arg constructor. Does that mean that no attributes must be set from new_instance(), either? You should be able to set whatever you like there. So... new_instance() could literally just be a call to 'self.class_()', with no other behavior, as long as the constructor requires no arguments? The modified __init__ that SA inserts won't be a problem there? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: association proxy usage from 0.3.11 to 0.4.4
Thanks Jason! It was nice meeting you at Pycon. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
