def show_room(room_id, db):
from sqlalchemy.orm.strategy_options import Load
opts = (
Load(m.Room)
.subqueryload(m.Room.listings)
.joinedload(m.RoomListing.occupancies),
Load(m.Occupancy)
.joinedload(m.Occupancy.review)
.subqueryload(m.Review.sections)
.joinedload(m.ReviewSection.refers_to),
Load(m.Occupancy)
.joinedload(m.Occupancy.photos)
)
try:
room = (db
.query(m.Room)
.options(*opts)
.outerjoin(m.Room.listings)
.outerjoin(m.Occupancy)
.filter(m.Room.id == room_id)
).one()
except NoResultFound:
raise HTTPError(404, "No matching room")
return template('room', room=room, ballot=get_ballot(db), version=request.query.v)
def get_samples(session, sample_ids=None):
query = session.query(Sample, SampleStats).outerjoin(
SampleStats,
and_(SampleStats.sample_id == Sample.id,
SampleStats.outliers == true(), SampleStats.full_reads == false(),
SampleStats.filter_type == 'all'))
if sample_ids is not None:
query = query.filter(SampleStats.sample_id.in_(sample_ids))
query = query.order_by(Sample.date).options(
Load(SampleStats).load_only('sequence_cnt', 'in_frame_cnt', 'stop_cnt',
'functional_cnt', 'no_result_cnt'))
result = []
for sample, stats in query:
sample_dict = _sample_to_dict(sample)
if stats is not None:
sample_dict['sequence_cnt'] = stats.sequence_cnt
sample_dict['functional_cnt'] = stats.functional_cnt
sample_dict['no_result_cnt'] = stats.no_result_cnt
sample_dict['total_cnt'] = stats.sequence_cnt + stats.no_result_cnt
result.append(sample_dict)
return result
def get_title_registers(title_numbers):
logger.debug('Start get_title_registers using {}'.format(title_numbers))
# Will retrieve matching titles that are not marked as deleted
fields = [TitleRegisterData.title_number.name, TitleRegisterData.register_data.name,
TitleRegisterData.geometry_data.name]
query = TitleRegisterData.query.options(Load(TitleRegisterData).load_only(*fields))
results = query.filter(TitleRegisterData.title_number.in_(title_numbers),
TitleRegisterData.is_deleted == false()).all()
logger.debug('Returning results: {}'.format(results))
logger.debug('End get_title_registers ')
return results
def get_mapped_lruprn(address_base_uprn):
logger.debug('Start get_mapped_lruprn using {}'.format(address_base_uprn))
result = UprnMapping.query.options(
Load(UprnMapping).load_only(
UprnMapping.lr_uprn.name,
UprnMapping.uprn.name
)
).filter(
UprnMapping.uprn == address_base_uprn
).first()
logger.debug('Returning result: {}'.format(result))
logger.debug('End get_mapped_lruprn')
return result
def get_official_copy_data(title_number):
logger.debug('Start get_official_copy_data using: {}'.format(title_number))
result = TitleRegisterData.query.options(
Load(TitleRegisterData).load_only(
TitleRegisterData.title_number.name,
TitleRegisterData.official_copy_data.name
)
).filter(
TitleRegisterData.title_number == title_number,
TitleRegisterData.is_deleted == false()
).first()
logger.debug('Returning result: {}'.format(result))
logger.debug('End get_official_copy_data')
return result
def show_ballot_event(ballot_id, ballot_type_name, db):
from sqlalchemy import func
from sqlalchemy.orm.strategy_options import Load
if ballot_type_name.lower() != ballot_type_name:
raise redirect(app.get_url(
'ballot-event',
ballot_id=ballot_id, ballot_type_name=ballot_type_name.lower()
))
ballot_type = db.query(m.BallotType).filter(func.lower(m.BallotType.name) == ballot_type_name.lower()).one()
listing_load = (
Load(m.BallotEvent)
.joinedload(m.BallotEvent.season)
.joinedload(m.BallotSeason.room_listings)
)
ballot_event = (db
.query(m.BallotEvent)
.join(m.BallotSeason)
.filter(m.BallotEvent.type == ballot_type)
.filter(m.BallotSeason.year == ballot_id)
.options(
listing_load
.joinedload(m.RoomListing.audience_types),
listing_load
.subqueryload(m.RoomListing.room),
# .joinedload(m.Room.parent),
# listing_load
# .subqueryload(m.RoomListing.occupancies),
Load(m.BallotEvent)
.subqueryload(m.BallotEvent.slots)
.joinedload(m.BallotSlot.person)
.subqueryload(m.Person.occupancies)
)
).one()
return template('ballot-event', event=ballot_event)
def all_rooms_q(self):
from sqlalchemy.orm import joinedload
from sqlalchemy.orm.strategy_options import Load
# circular import is guarded by function
from . import Room, RoomListing, Occupancy
# this should autoload all the subplaces of this place
descendant_places = (object_session(self).query(Place).filter(
Place.id == self.id).cte(name='descendant_places', recursive=True))
descendant_places = descendant_places.union_all(
object_session(self).query(Place).filter(
Place.parent_id == descendant_places.c.id))
# make this not ridiculously slow
opts = (Load(Room).joinedload(Room.listing_for).joinedload(
RoomListing.occupancies).load_only(Occupancy.resident_id),
Load(Room).joinedload(Room.listing_for).subqueryload(
RoomListing.audience_types), Load(Room).joinedload(
Room.listing_for).undefer(RoomListing.bad_listing),
Load(Room).subqueryload(Room.stats))
return object_session(self).query(Room).join(
descendant_places).options(*opts)
def get_load_options(*columns):
"""Helper method that attempts to extract a sqlalchemy object from
`columns[0]` and return remaining columns to apply to a query load method.
"""
model_inspect = inspect(columns[0], raiseerr=False)
# return an obj which has loading API
if model_inspect and model_inspect.is_mapper:
obj = Load(columns[0])
columns = columns[1:]
elif isinstance(columns[0], Load):
obj = columns[0]
columns = columns[1:]
else:
obj = orm
return (obj, columns)
def show_ballot_control_panel(ballot_id, ballot_type_name, db):
if ballot_type_name.lower() != ballot_type_name:
raise redirect(
app.get_url('edit-ballot-event',
ballot_id=ballot_id,
ballot_type_name=ballot_type_name.lower()))
ballot_type = db.query(m.BallotType).filter(
func.lower(m.BallotType.name) == ballot_type_name.lower()).one()
ballot_event = (db.query(m.BallotEvent).join(
m.BallotSeason).filter(m.BallotEvent.type == ballot_type).filter(
m.BallotSeason.year == ballot_id).options(
Load(m.BallotEvent).joinedload(
m.BallotEvent.season).joinedload(
m.BallotSeason.room_listings).joinedload(
m.RoomListing.audience_types))).one()
return template('ballot-event-edit', ballot_event=ballot_event)
def get_title_number_and_register_data(lr_uprn):
logger.debug('Start get_title_number_and_register_data using: {}'.format(lr_uprn))
amended_lr_uprn = '{' + lr_uprn + '}'
result = TitleRegisterData.query.options(
Load(TitleRegisterData).load_only(
TitleRegisterData.lr_uprns,
TitleRegisterData.title_number,
TitleRegisterData.register_data
)
).filter(
TitleRegisterData.lr_uprns.contains(amended_lr_uprn),
TitleRegisterData.is_deleted == false()
).all()
logger.debug('Returning result: {}'.format(result))
logger.debug('End get_title_number_and_register_data')
if result:
return result[0]
else:
return None
def get_title_register(title_number):
if title_number:
logger.debug('Start get_title_register using {}'.format(title_number))
# Will retrieve the first matching title that is not marked as deleted
result = TitleRegisterData.query.options(
Load(TitleRegisterData).load_only(
TitleRegisterData.title_number.name,
TitleRegisterData.register_data.name,
TitleRegisterData.geometry_data.name
)
).filter(
TitleRegisterData.title_number == title_number,
TitleRegisterData.is_deleted == false()
).first()
logger.debug('Returning result: {}'.format(result))
logger.debug('End get_title_register')
return result
else:
logger.debug('End get_title_register - No title number received')
raise TypeError('Title number must not be None.')
def undefered_query(self):
"""Gets the query with all attributes undefered."""
return self.query.options(Load(self.Model).undefer('*'))