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_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(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 __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 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)))
