the Query will consolidate multiple rows with the same primary key into a
single object instance - comparing the rows returned by the two different
approaches will reveal the source of the issue. set echo='debug' to see
that output.
Alisue wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
