def _get_result_Flight_User_byClub(year=None): '''return the number of users in flights that are recorded under the club id. Rankable flight means public flight. Sort by number of flights this year''' query = db.session.query( \ Flight.club_id, \ func.count((Flight.pilot_id.distinct())).label('users_count') \ , func.count(Flight.id).label('flights_count')) \ .group_by(Flight.club_id) if isinstance(year, int): # if year is None, then get flights for all seasons year_start = date(year, 1, 1) year_end = date(year, 12, 31) query = query.filter(Flight.date_local >= year_start)\ .filter(Flight.date_local <= year_end) subq = query.subquery() result = db.session.query( Club, subq.c.flights_count, subq.c.users_count, over(func.rank(), order_by=desc("flights_count")).label("rank"), ).join((subq, getattr(subq.c, "club_id") == Club.id)) return result
def get_creators(document_ids): """ Get the creator for the list of given document ids. """ t = DBSession.query( ArchiveDocument.document_id.label('document_id'), User.id.label('user_id'), User.name.label('name'), over( func.rank(), partition_by=ArchiveDocument.document_id, order_by=HistoryMetaData.id).label('rank')). \ select_from(ArchiveDocument). \ join( DocumentVersion, and_( ArchiveDocument.document_id == DocumentVersion.document_id, ArchiveDocument.version == 1)). \ join(HistoryMetaData, DocumentVersion.history_metadata_id == HistoryMetaData.id). \ join(User, HistoryMetaData.user_id == User.id). \ filter(ArchiveDocument.document_id.in_(document_ids)). \ subquery('t') query = DBSession.query( t.c.document_id, t.c.user_id, t.c.name). \ filter(t.c.rank == 1) return { document_id: { 'name': name, 'user_id': user_id } for document_id, user_id, name in query }
def _get_result_Flight(table, table_column, year=None): subq = ( db.session.query( getattr(Flight, table_column), func.count("*").label("count"), func.sum(Flight.index_score).label("total"), ).group_by(getattr(Flight, table_column)).outerjoin( Flight.model) #glider model .filter(Flight.is_rankable())) if isinstance(year, int): year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start).filter( Flight.date_local <= year_end) subq = subq.subquery() result = db.session.query( table, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank"), ).join((subq, getattr(subq.c, table_column) == table.id)) if table == User: result = result.outerjoin(table.club) result = result.options(eagerload(table.club)) return result
def __get_result(model, flight_field, **kw): subq = DBSession \ .query(getattr(Flight, flight_field), func.count('*').label('count'), func.sum(Flight.index_score).label('total')) \ .group_by(getattr(Flight, flight_field)) \ .outerjoin(Flight.model) if 'year' in kw: try: year = int(kw['year']) except: raise HTTPBadRequest year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start) \ .filter(Flight.date_local <= year_end) subq = subq.subquery() result = DBSession \ .query(model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc('total')).label('rank')) \ .join((subq, getattr(subq.c, flight_field) == model.id)) result = result.order_by(desc('total')) return result
def _get_result(model, flight_field, year=None): subq = db.session \ .query(getattr(Flight, flight_field), func.count('*').label('count'), func.sum(Flight.index_score).label('total')) \ .group_by(getattr(Flight, flight_field)) \ .outerjoin(Flight.model) if isinstance(year, int): year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start) \ .filter(Flight.date_local <= year_end) subq = subq.subquery() result = db.session \ .query(model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc('total')).label('rank')) \ .join((subq, getattr(subq.c, flight_field) == model.id)) if model == User: result = result.options(subqueryload(model.club)) result = result.order_by(desc('total')) return result
def _get_result(model, flight_field, year=None): subq = ( db.session.query( getattr(Flight, flight_field), func.count("*").label("count"), func.sum(Flight.index_score).label("total"), ) .group_by(getattr(Flight, flight_field)) .outerjoin(Flight.model) .filter(Flight.is_rankable()) ) if isinstance(year, int): year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start).filter( Flight.date_local <= year_end ) subq = subq.subquery() result = db.session.query( model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank"), ).join((subq, getattr(subq.c, flight_field) == model.id)) if model == User: result = result.outerjoin(model.club) result = result.options(eagerload(model.club)) return result
def __get_result(model, flight_field, **kw): subq = ( DBSession.query( getattr(Flight, flight_field), func.count("*").label("count"), func.sum(Flight.index_score).label("total"), ) .group_by(getattr(Flight, flight_field)) .outerjoin(Flight.model) ) if "year" in kw: try: year = int(kw["year"]) except: raise HTTPBadRequest year_start = date(year, 1, 1) year_end = date(year, 12, 31) subq = subq.filter(Flight.date_local >= year_start).filter(Flight.date_local <= year_end) subq = subq.subquery() result = DBSession.query( model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank") ).join((subq, getattr(subq.c, flight_field) == model.id)) result = result.order_by(desc("total")) return result
def __load(self): query = self.add_columns(over(func.count(1)).label('_count')) res = [tup[0] for tup in Query.__iter__(query)] if len(res) > 0: count = tup._count # pylint:disable-msg=W0212,W0631 else: count = 0 return count, res
def __get_result(model, flight_field): subq = ( DBSession.query( getattr(Flight, flight_field), func.count("*").label("count"), func.sum(Flight.index_score).label("total"), ) .group_by(getattr(Flight, flight_field)) .outerjoin(Flight.model) .subquery() ) result = DBSession.query( model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank") ).join((subq, getattr(subq.c, flight_field) == model.id)) result = result.order_by(desc("total")) return result
def set_author(outings, lang): """Set the author (the user who created an outing) on a list of outings. """ if not outings: return outing_ids = [o.document_id for o in outings] t = DBSession.query( ArchiveDocument.document_id.label('document_id'), User.id.label('user_id'), User.username.label('username'), User.name.label('name'), over( func.rank(), partition_by=ArchiveDocument.document_id, order_by=HistoryMetaData.id).label('rank')). \ select_from(ArchiveDocument). \ join( DocumentVersion, and_( ArchiveDocument.document_id == DocumentVersion.document_id, ArchiveDocument.version == 1)). \ join(HistoryMetaData, DocumentVersion.history_metadata_id == HistoryMetaData.id). \ join(User, HistoryMetaData.user_id == User.id). \ filter(ArchiveDocument.document_id.in_(outing_ids)). \ subquery('t') query = DBSession.query( t.c.document_id, t.c.user_id, t.c.username, t.c.name). \ filter(t.c.rank == 1) author_for_outings = { document_id: { 'username': username, 'name': name, 'user_id': user_id } for document_id, user_id, username, name in query } for outing in outings: outing.author = author_for_outings.get(outing.document_id)
def _remove_locale_versions(document_id, lang): # Only history metadata not shared with other locales should be removed. # This subquery gets the list of history_metadata_id with their lang and # number of associated locales: t = DBSession.query( DocumentVersion.history_metadata_id, DocumentVersion.lang, over( func.count('*'), partition_by=DocumentVersion.history_metadata_id).label('cnt')). \ filter(DocumentVersion.document_id == document_id). \ subquery('t') # Gets the list of history_metadata_id associated only # to the current locale: history_metadata_ids = DBSession.query(t.c.history_metadata_id). \ filter(t.c.lang == lang).filter(t.c.cnt == 1).all() DBSession.query(DocumentVersion). \ filter(DocumentVersion.document_id == document_id). \ filter(DocumentVersion.lang == lang).delete() if len(history_metadata_ids): DBSession.execute(HistoryMetaData.__table__.delete().where( HistoryMetaData.id.in_(history_metadata_ids)))