python - 如何创建使用 groupby 的 column_properties？

Question

我有这个 sql 查询:

select
    rooms.*,
    COUNT(DISTINCT(o.resident_id)) as resident_count,
    COUNT(reviews.id) as review_count,
    COUNT(photos.id) as photo_count,
    AVG(reviews.rating) as mean_review  
from
    t_rooms rooms
JOIN
    t_room_listings listings on listings.room_id = rooms.id
JOIN
    t_occupancies o on o.listing_id = listings.id 
LEFT JOIN
    t_reviews reviews on reviews.occupancy_id = o.id
LEFT JOIN
    t_photos photos on photos.occupancy_id = o.id
GROUP BY rooms.id

我知道我可以用 ORM 查询形式写成:

q = (session
    .query(
        Room,
        func.count(func.distinct(Occupancy.resident_id)).label('resident_count'),
        func.count(Review.id).label('review_count'),
        func.count(Photo.id).label('photo_count'),
        (
            (3 + func.avg(Review.rating)) / (1 + func.count(Review.rating))
        ).label('bayesian_rating')
    )
    .select_from(
        join(Room, RoomListing).join(Occupancy).outerjoin(Review).outerjoin(Photo)
    )
    .group_by(Room.id)
)

for room, res_ct, rev_ct, p_ct in q:
    wish_that_I_could_write(room.res_ct, room.rev_ct, room.p_ct, room.score)

但是我如何在我的 Room 类中将 resident_count、review_count 等声明为 column_property，以便我不需要每次都构建这个查询？

Answer 1

您可以像这样通过将查询映射到对象来实现此结果:

class ExtendedRoom(object):
    pass


# q is your query
mapper(ExtendedRoom, q.statement.alias())
for room in session.query(ExtendedRoom).all():
    # now room have review_count and other attributes
    print(room.review_count)

这里是使用 column_property 的简化示例。

from sqlalchemy import create_engine, Column, Integer, MetaData, Table, String, func
from sqlalchemy.sql import select
from sqlalchemy.orm import sessionmaker,  mapper, column_property
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
session = Session()
metadata = MetaData()


room = Table('room', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),  
    Column('num', Integer, default=0),  
)
metadata.create_all(engine)
statement = select([room]).group_by(room.c.id).alias()


class Room(object):
    pass


mapper(Room, statement, properties={
    'count': column_property(func.count(statement.c.num)),
})


print(session.query(Room).all())