How can I create column_properties properties that use the group?
I have this sql query:
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
What I know I can write in the ORM request form as:
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)
But how can I declare resident_count
, review_count
etc. like column_property
in my class Room
, so I don't have to create this query every time?
+3
source to share
1 answer
You can achieve this result by querying a mapping to an object like this:
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)
Here's a simplified example with 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())
+1
source to share