SQLAlchemy: 0.5.2
SQLite: 3.5.9
MySQL: 5.0.67-0ubuntu6
To short. The problem is 'The result come differently from session's
query and plain sql which built by session's query.'
Well... It is too difficult to explain the situation so just have a
look my code first please.
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id) &
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
raise str(len(query.all()))
>> 1 - So stupid. Should be '10'.
However. The result should be '10' because ...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id) &
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
# raise str(len(query.all()))
sql = str(query)
query = meta.Session.query(model.Room).from_statement(sql)
raise str(len(query.all()))
>> 10 - This is correct.
Or execute <sql> (written bottom of this post, built by session's
query) on Terminal with sqlite3/MySQL return '10' as well (when I
count the rows).
I'm using SQLAlchemy on Pylons 0.9.7 and the module named 'paginator'
doesn't allow to pass the data type of 'SQLAlchemy 0.5.2 's select
object (SQL Expression Table Data). That's why (and else) I need to
use ORM. (And I don't know that even SQL Expression could return
correct value or not.)
Does anyone have idea? Or is it just bug and i have to wait to be
fixed this?
By the way. I'm using 'declarative_base()' to build my ORM.
Thanks. and sorry for my horrible english :-(
<sql>
SELECT rooms.id AS rooms_id, rooms.name AS rooms_name,
rooms.space_utilized AS rooms_space_utilized, rooms.floor AS
rooms_floor, rooms.direction AS rooms_direction, rooms.reference_url
AS rooms_reference_url, rooms.remarks AS rooms_remarks,
rooms.private_remarks AS rooms_private_remarks, rooms.is_recommand AS
rooms_is_recommand, rooms.advertisement AS rooms_advertisement,
rooms.has_parking_space AS rooms_has_parking_space,
rooms.immediately_moveable AS rooms_immediately_moveable,
rooms.arrangement AS rooms_arrangement, rooms.arrangement_detail AS
rooms_arrangement_detail, rooms.house_rent AS rooms_house_rent,
rooms.security_deposit AS rooms_security_deposit, rooms.key_money AS
rooms_key_money, rooms.right_money AS rooms_right_money,
rooms.management_fee AS rooms_management_fee, rooms.communal_charge AS
rooms_communal_charge, rooms.parking_charge AS rooms_parking_charge,
rooms.property_id AS rooms_property_id, rooms.company_id AS
rooms_company_id, rooms.created AS rooms_created, rooms.modified AS
rooms_modified
FROM rooms JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_id
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
