Hey thanks! I figured out but I still don't know why...
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)
# The lines below are the magic.
subquery = query.subquery()
query = meta.Session.query(model.Room)
query = query.join((subquery, model.Room.id == subquery.c.id))
I found it because 'len(query.all())' return the 'Correct Value' but
'query.count()'
How did it happen? I cant' understand....
On 3月17日, 午前2:45, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
