I want to use order by and limit in a sub select, but it doesn't seem
to work:
---- Code ----
import pkg_resources
pkg_resources.require( "sqlalchemy" )
pkg_resources.require( "pysqlite" )
from sqlalchemy import *
metadata = BoundMetaData( 'sqlite:////tmp/test.db' )
metadata.engine.echo = True
class A( object ):
pass
class B( object ):
pass
A.table = Table( "table_a", metadata,
Column( "id", Integer, primary_key=True),
Column( "name", String(20) ) )
B.table = Table( "table_b", metadata,
Column( "id", Integer, primary_key=True),
Column( "name", String(20) ),
Column( "table_a_id", Integer, ForeignKey( "table_a.id" ) ),
Column( "score", Float ) )
mapper( A, A.table )
mapper( B, B.table )
print "Get the 10 highest scoring rows from B:"
print select( [B.c.name], from_obj=[B.table], order_by=[ B.c.score ],
limit=10 )
print "Get the 10 highest scoring rows from B, join to A and reorder"
print select( [A.c.name, B.c.name, B.c.score],
B.c.id.in_( select( [B.c.id], from_obj=[B.table],
order_by=[ B.c.score ], limit=10, correlate=False ) ),
from_obj=[ A.table.join( B.table ) ],
order_by = [ A.c.id, B.c.id ] )
---- Output ----
Get the 10 highest scoring rows from B:
SELECT table_b.name
FROM table_b ORDER BY table_b.score
LIMIT 10 OFFSET 0
Get the 10 highest scoring rows from B, join to A and reorder
SELECT table_a.name, table_b.name, table_b.score
FROM table_a JOIN table_b ON table_a.id = table_b.table_a_id
WHERE table_b.id IN (SELECT table_b.id AS id
FROM table_b
LIMIT 10 OFFSET 0) ORDER BY table_a.id, table_b.id
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
