I juts commited a tweak in 1387 to make this easier. your test
program can do:
ret = m.select("city_name = 'Rome'", from_obj=[cities], limit=10)
giving you:
SELECT users.city_id AS users_city_id, users.user_name AS
users_user_name, city_e54b.city_id AS city_e54b_city_id,
city_e54b.city_name AS city_e54b_city_name, city_e54b.country AS
city_e54b_country, users.user_id AS users_user_id
FROM (SELECT users.user_id AS users_user_id
FROM users, city
WHERE city_name = 'Rome'
LIMIT 10 OFFSET 0) AS rowcount, users LEFT OUTER JOIN city AS
city_e54b ON city_e54b.city_id = users.city_id
WHERE rowcount.users_user_id = users.user_id
or more correctly:
ret = m.select("city.city_name = 'Rome' and
city.city_id=users.city_id", from_obj=[cities], limit=10)
SELECT users.city_id AS users_city_id, users.user_name AS
users_user_name, city_e54b.city_id AS city_e54b_city_id,
city_e54b.city_name AS city_e54b_city_name, city_e54b.country AS
city_e54b_country, users.user_id AS users_user_id
FROM (SELECT users.user_id AS users_user_id
FROM users, city
WHERE city.city_name = 'Rome' and city.city_id=users.city_id
LIMIT 10 OFFSET 0) AS rowcount, users LEFT OUTER JOIN city AS
city_e54b ON city_e54b.city_id = users.city_id
WHERE rowcount.users_user_id = users.user_id
On May 4, 2006, at 6:30 AM, Sandro Dentella wrote:
Hi everybody,
in the script reported below mapper.select will fail in presence of
'limit' parameter if the condition is given as literal rather
than with
column object and the mapper is a join:
ret = m.select(cities.c.city_name == 'Rome' , limit=10)
#ret = m.select("city_name = 'Rome'" , limit=10) ### FAILS
on the other hand
ret = m.select("user_id > 2", limit=10)
will also work, since the field on which I filter is from the first
table
of the join. If I understand this depends on the way limit is
implemented.
If I understand correctly the docs, sqlalchemy will try to limit on
the
number of instances of the first table while I'd like to limit on the
resulting rowset, is there any way of achieving this?
Thanks in advance
sandro
*:-)
from sqlalchemy import *
#eng = create_engine("sqlite://filename=test-sql.db", echo=False)
eng = create_engine("sqlite://", echo=False)
cities = Table('city', eng,
Column('city_id', Integer, primary_key = True),
Column('city_name', String(30)),
Column('country', String(30), nullable = False)
)
users = Table('users', eng,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(30), nullable = False),
Column('city_id', String(30), ForeignKey
("city.city_id"), nullable = False),
)
users.create()
users.insert().execute(
{'user_id': 1 , 'user_name': 'Sam', 'city_id' : '1' },
{'user_id': 2 , 'user_name': 'Sid', 'city_id' : '1' },
{'user_id': 3 , 'user_name': 'Axe', 'city_id' : '2' },
{'user_id': 4 , 'user_name': 'Ted', 'city_id' : '2' })
cities.create()
cities.insert().execute(
{'city_id': 1, 'city_name': 'Rome', 'country': 'Italy'},
{'city_id': 2, 'city_name': 'Paris', 'country': 'France'},
)
class User(object): pass
class City(object): pass
c2 = mapper(City, cities)
rel2 = relation(c2, lazy=False, )
m = mapper(User, users, properties = { 'cities' : rel2 } )
ret = m.select("user_id > 2", limit=10)
ret = m.select(cities.c.city_name == 'Rome' , limit=10)
#ret = m.select("city_name = 'Rome'" , limit=10)
ret = m.select("city_name = :city_name" , params={'city_name' :
'Rome'})
--
Sandro Dentella *:-)
e-mail: [EMAIL PROTECTED]
http://www.tksql.org TkSQL Home page - My GPL work
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services,
security?
Get stuff done quickly with pre-integrated technology to make your
job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache
Geronimo
http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
