def GetJoinTable(self, searchInfo):
ObservationTable = Base.metadata.tables['Observation']
obsValTable = Base.metadata.tables['observationdynpropvaluenow']
joinTable = super().GetJoinTable(searchInfo)
joinTable = outerjoin(joinTable,
ObservationTable,
ObservationTable.c['FK_Station'] == Station.ID)
joinTable = outerjoin(joinTable,
obsValTable,
and_(ObservationTable.c['ID'] == obsValTable.c['FK_Observation'], obsValTable.c['Name'] == 'nom_vernaculaire'))
self.selectable.append(
func.string_agg(
obsValTable.c['ValueString'],
aggregate_order_by(literal_column("','"), Station.ID
)).label('nom_vernaculaire'))
self.selectable.append(
func.string_agg(
ObservationTable.c['taxon'],
aggregate_order_by(literal_column("','"), Station.ID
)).label('nom_latin'))
return joinTable
def builder(t_from, t_to, completed, invalidated):
# aggregation query
q = sa.select([
sa.func.time_bucket(t_from.c.time, sa.text(
"'%s'" % interval)).label('time'),
sa.func.first(pg.aggregate_order_by(t_from.c.open,
t_from.c.time)).label('open'),
sa.func.max(t_from.c.high).label('high'),
sa.func.min(t_from.c.low).label('low'),
sa.func.last(pg.aggregate_order_by(t_from.c.close,
t_from.c.time)).label('close'),
sa.func.sum(t_from.c.volume).label('volume'),
sa.func.sum(t_from.c.value).label('value')
],
from_obj=t_from).group_by(
sa.text('1')).where(t_from.c.time <= invalidated)
if completed is not None:
q = q.where(t_from.c.time >= completed)
# upsert materialized table
inst = pg.insert(t_to).from_select(t_to.columns, q)
inst = inst.on_conflict_do_update(index_elements=[t_kline_1m.c.time],
set_={
'open': inst.excluded.open,
'high': inst.excluded.high,
'low': inst.excluded.low,
'close': inst.excluded.close,
'volume': inst.excluded.volume,
'value': inst.excluded.value,
})
print(inst.compile(dialect=pg.dialect()))
return inst
def get_by_player(playerid):
q = db.session.query(
#RaidDropsModel.id,
RaidDropsModel.name.label('playername'),
#RaidDropsModel.world.label('playerworld'),
db.func.array_agg(aggregate_order_by(RaidDropsModel.time, ItemModel.name)).label('time'),
db.func.array_agg(aggregate_order_by(RaidDropsModel.classjob, ItemModel.name)).label('job'),
#db.func.array_agg(RaidDropsModel.itemid).label('idd'),
#RaidDropsModel.itemquantity,
db.func.array_agg(aggregate_order_by(ItemModel.rarity, ItemModel.name)).label('rarity'),
db.func.array_agg(aggregate_order_by(ItemModel.name, ItemModel.name)).label('itemnames'),
db.func.array_agg(aggregate_order_by(ItemModel.id, ItemModel.name)).label('ids'),
db.func.array_agg(aggregate_order_by(RaidDropsModel.itemquantity, ItemModel.name)).label('itemquantities'),
db.func.array_agg(aggregate_order_by(ItemModel.itemuicategory, ItemModel.name)).label('itemcategories'),
db.func.array_agg(aggregate_order_by(ItemModel.equipslotcategory, ItemModel.name)).label('equipcategories'),
db.func.array_agg(aggregate_order_by(ItemModel.icon, ItemModel.name)).label('icons'),
#ItemModel.name.label('itemname'),
#ItemModel.icon.label('itemicon'),
#ItemModel.description.label('itemdescription'),
#ItemModel.equipslotcategory.label('itemequipslot'),
#ItemModel.itemuicategory.label('itemcategory'),
).join(ItemModel).group_by(
RaidDropsModel.name
).filter(RaidDropsModel.playerid == str(playerid),
RaidDropsModel.view == True)
return q
def db_detail(prodid):
db_conn = None
db_conn = db_engine.connect()
query1 = select(
[
db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price,
db_prod.c.description,
func.string_agg(
db_acts.c.actorname,
aggregate_order_by(literal_column("'; '"),
db_acts.c.actorname))
],
distinct=True).where(
and_(db_films.c.movieid == db_prod.c.movieid,
db_prod.c.prod_id == prodid,
db_actfilm.c.actorid == db_acts.c.actorid,
db_actfilm.c.movieid == db_films.c.movieid)).group_by(
db_films.c.movietitle, db_prod.c.prod_id)
result1 = db_conn.execute(query1)
query2 = select(
[
func.string_agg(
db_genres.c.genrename,
aggregate_order_by(literal_column("', '"),
db_genres.c.genrename))
],
distinct=True).where(
and_(db_prod.c.prod_id == prodid,
db_filmgen.c.movieid == db_prod.c.movieid,
db_genres.c.genreid == db_filmgen.c.genreid)).group_by(
db_prod.c.prod_id)
result2 = db_conn.execute(query2)
query3 = select(
[
func.string_agg(
db_dirs.c.directorname,
aggregate_order_by(literal_column("'; '"),
db_dirs.c.directorname))
],
distinct=True).where(
and_(db_prod.c.prod_id == prodid,
db_dirfilm.c.movieid == db_prod.c.movieid,
db_dirs.c.directorid == db_dirfilm.c.directorid)).group_by(
db_prod.c.prod_id)
result3 = db_conn.execute(query3)
result1 = list(result1)
result1 = list(result1[0])
result2 = list(result2)
result3 = list(result3)
result1.append(result2[0][0])
result1.insert(4, result3[0][0])
db_conn.close()
return result1
def _generate_selectable():
cte = select([
EndpointSIP.uuid.label('uuid'),
literal(0).label('level'),
literal('0', String).label('path'),
EndpointSIP.uuid.label('root'),
]).cte(recursive=True)
endpoints = cte.union_all(
select([
EndpointSIPTemplate.parent_uuid.label('uuid'),
(cte.c.level + 1).label('level'),
(cte.c.path + cast(
func.row_number().over(
partition_by='level',
order_by=EndpointSIPTemplate.priority,
),
String,
)).label('path'),
(cte.c.root),
]).select_from(
join(cte, EndpointSIPTemplate,
cte.c.uuid == EndpointSIPTemplate.child_uuid)))
return (select([
endpoints.c.root,
cast(
func.jsonb_object(
func.array_agg(
aggregate_order_by(
EndpointSIPSectionOption.key,
endpoints.c.path.desc(),
)),
func.array_agg(
aggregate_order_by(
EndpointSIPSectionOption.value,
endpoints.c.path.desc(),
)),
),
JSONB,
).label('options'),
]).select_from(
join(
endpoints,
EndpointSIPSection,
EndpointSIPSection.endpoint_sip_uuid == endpoints.c.uuid,
).join(
EndpointSIPSectionOption,
EndpointSIPSectionOption.endpoint_sip_section_uuid ==
EndpointSIPSection.uuid,
)).group_by('root'))
def ballot_retrieval_list(jurisdiction: Jurisdiction, round: Round) -> str:
previous_ballots = set(
SampledBallotDraw.query.join(Round).filter(
Round.round_num < round.round_num).join(SampledBallot).join(
Batch).filter_by(jurisdiction_id=jurisdiction.id).values(
Batch.name, SampledBallot.ballot_position))
ballots = (SampledBallotDraw.query.filter_by(
round_id=round.id).join(SampledBallot).join(Batch).filter_by(
jurisdiction_id=jurisdiction.id).join(AuditBoard).group_by(
AuditBoard.id, SampledBallot.id, Batch.id).order_by(
AuditBoard.name, Batch.name,
SampledBallot.ballot_position).values(
Batch.name,
SampledBallot.ballot_position,
Batch.storage_location,
Batch.tabulator,
func.string_agg(
SampledBallotDraw.ticket_number,
aggregate_order_by(
literal_column("','"),
SampledBallotDraw.ticket_number),
),
AuditBoard.name,
))
csv_io = io.StringIO()
retrieval_list_writer = csv.writer(csv_io)
retrieval_list_writer.writerow([
"Batch Name",
"Ballot Number",
"Storage Location",
"Tabulator",
"Ticket Numbers",
"Already Audited",
"Audit Board",
])
for ballot in ballots:
(
batch_name,
position,
storage_location,
tabulator,
ticket_numbers,
audit_board_name,
) = ballot
previously_audited = "Y" if (batch_name,
position) in previous_ballots else "N"
retrieval_list_writer.writerow([
batch_name,
position,
storage_location,
tabulator,
ticket_numbers,
previously_audited,
audit_board_name,
])
return csv_io.getvalue()
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(
self):
case_study_query = (db.session.query(
CaseStudy.supplier_code.label('supplier_code'),
CaseStudy.data['service'].astext.label('domain'),
func.count(CaseStudy.id).label('count')).group_by(
CaseStudy.supplier_code, CaseStudy.data['service'].astext))
subquery = (case_study_query.intersect(
case_study_query.filter(
CaseStudy.status == 'rejected')).subquery())
results = (db.session.query(
Supplier.id, Supplier.code, Supplier.name,
func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label(
'domains')).join(SupplierDomain, Domain).join(
subquery,
and_(Supplier.code == subquery.columns.supplier_code,
Domain.name == subquery.columns.domain)).filter(
Supplier.status != 'deleted',
Supplier.data['recruiter'].astext.in_(
['no', 'both']),
SupplierDomain.status == 'unassessed').group_by(
Supplier.id, Supplier.code,
Supplier.name).all())
return [r._asdict() for r in results]
def get_ips_ports(self, flt, limit=None, skip=None):
req = flt.query(select([self.tables.scan.id]))
if skip is not None:
req = req.offset(skip)
if limit is not None:
req = req.limit(limit)
base = req.cte("base")
return ({
"addr":
rec[2],
"starttime":
rec[1],
"ports": [{
"proto": proto,
"port": int(port),
"state_state": state
} for proto, port, state in (
elt.split(',')
for elt in ''.join(rec[0])[3:-3].split(')","('))]
} for rec in self.db.execute(
select([
func.array_agg(
postgresql.aggregate_order_by(
tuple_(self.tables.port.protocol, self.tables.port.
port, self.tables.port.state).label('a'),
tuple_(self.tables.port.protocol, self.tables.port.port
).label('a'))).label('ports'),
self.tables.scan.time_start,
self.tables.scan.addr,
]).select_from(join(self.tables.port, self.tables.scan)).group_by(
self.tables.scan.addr, self.tables.scan.time_start).where(
and_(self.tables.port.port >= 0,
self.tables.scan.id.in_(base)))))
def get_group_team_stats(self, replay_ids, session=None):
query = session.query(
PlayerGame.game,
func.array_agg(
aggregate_order_by(
PlayerGame.player,
PlayerGame.player)).label("team")).filter(
PlayerGame.game.in_(replay_ids)).group_by(
PlayerGame.game).group_by(
PlayerGame.is_orange).subquery()
teams = session.query(query.c.team, func.array_agg(
query.c.game)).group_by(query.c.team).all()
return {
"teamStats": [{
"team":
team[0],
"games":
team[1],
"names": [
name
for (name, ) in session.query(func.min(
PlayerGame.name)).filter(PlayerGame.game.in_(team[1])).
filter(PlayerGame.player.in_(team[0])).order_by(
PlayerGame.player).group_by(PlayerGame.player).all()
],
**self._create_group_stats(session,
player_filter=team[0],
replay_ids=team[1])
} for team in teams]
}
def get_skillEntity(conn: Connection,
skill_ids: Iterable[int]) -> list[SkillEntityNoReverse]:
mstSkillLvJson = (select(
mstSkillLv.c.skillId,
func.jsonb_agg(
aggregate_order_by(mstSkillLv.table_valued(),
mstSkillLv.c.lv)).label(mstSkillLv.name),
).where(mstSkillLv.c.skillId.in_(skill_ids)).group_by(
mstSkillLv.c.skillId).cte())
JOINED_SKILL_TABLES = (mstSkill.outerjoin(
mstSkillDetail, mstSkillDetail.c.id == mstSkill.c.id).outerjoin(
mstSvtSkill, mstSvtSkill.c.skillId == mstSkill.c.id).outerjoin(
mstSkillLvJson, mstSkillLvJson.c.skillId == mstSkill.c.id))
SELECT_SKILL_ENTITY = [
func.to_jsonb(mstSkill.table_valued()).label(mstSkill.name),
sql_jsonb_agg(mstSkillDetail),
sql_jsonb_agg(mstSvtSkill),
mstSkillLvJson.c.mstSkillLv,
]
stmt = (select(
*SELECT_SKILL_ENTITY).select_from(JOINED_SKILL_TABLES).where(
mstSkill.c.id.in_(skill_ids)).group_by(
mstSkill.c.id, mstSkillLvJson.c.mstSkillLv))
skill_entities = (SkillEntityNoReverse.from_orm(skill)
for skill in conn.execute(stmt).fetchall())
order = {skill_id: i for i, skill_id in enumerate(skill_ids)}
return sorted(skill_entities, key=lambda skill: order[skill.mstSkill.id])
def get_rates_by_dates_for_currency_in_period(self, currency, start_date, end_date):
"""
:type currency: str
:type start_date: datetime.date
:type end_date: datetime.date
:rtype: dict[datetime.date, list[decimal.Decimal]]
"""
result = self.db_session\
.query(
ExchangeRate.date,
func.array_agg(aggregate_order_by(ExchangeRate.rate, ExchangeRate.provider_id.asc()))
)\
.filter(
and_(
ExchangeRate.date >= start_date,
ExchangeRate.date <= end_date,
ExchangeRate.currency == currency,
ExchangeRate.rate.isnot(None)
)
)\
.group_by(ExchangeRate.date)\
.order_by(ExchangeRate.date)\
.all()
return {r[0]: list(r[1]) for r in result}
def _group_lines_stops(list_stops):
""" Groups lines and stops such that each distinct line and direction has
a group of stops associated with it.
"""
stops = [s.atco_code for s in list_stops]
separator = db.literal_column("' / '")
destinations = db.func.string_agg(
db.distinct(models.JourneyPattern.destination),
pg.aggregate_order_by(separator, models.JourneyPattern.destination)
)
array_stops = pg.array_agg(db.distinct(models.JourneyLink.stop_point_ref))
groups = (
db.session.query(
models.Service.code.label("code"),
models.JourneyPattern.direction.label("direction"),
models.Service.line.label("line"),
destinations.label("destination"),
array_stops.label("stops")
)
.select_from(models.Service)
.join(models.Service.patterns)
.join(models.JourneyPattern.links)
.filter(models.JourneyLink.stop_point_ref.in_(stops))
.group_by(models.Service.code, models.Service.line,
models.JourneyPattern.direction)
.order_by(models.Service.line, models.JourneyPattern.direction)
.all()
)
return [g._asdict() for g in groups]
def fill_description():
delimiter = sa.bindparam("delimiter", " – ")
separator = sa.bindparam("separator", " / ")
origin = sa.func.string_agg(
sa.distinct(pattern.c.origin),
sa_pg.aggregate_order_by(separator, pattern.c.origin))
destination = sa.func.string_agg(
sa.distinct(pattern.c.origin),
sa_pg.aggregate_order_by(separator, pattern.c.origin))
query = (sa.select([
service.c.id.label("id"),
(origin + delimiter + destination).label("description")
]).where((service.c.description == "")
& (service.c.id == pattern.c.service_ref)
& ~pattern.c.direction).group_by(service.c.id).alias("fill_dest"))
return query
def build_column(self):
table_column = column(self.column_name)
if self.order_by_col:
order_by_column = column(self.order_by_col)
return func.array_agg(
aggregate_order_by(table_column,
order_by_column.asc())).label(self.label)
return func.array_agg(table_column).label(self.label)
class OptimizationProcedureORM(ProcedureMixin, BaseResultORM):
"""
An Optimization procedure
"""
__tablename__ = 'optimization_procedure'
id = Column(Integer, ForeignKey('base_result.id', ondelete='cascade'), primary_key=True)
def __init__(self, **kwargs):
kwargs.setdefault("version", 1)
self.procedure = "optimization"
super().__init__(**kwargs)
schema_version = Column(Integer, default=1)
initial_molecule = Column(Integer, ForeignKey('molecule.id'))
initial_molecule_obj = relationship(MoleculeORM, lazy='select', foreign_keys=initial_molecule)
# # Results
energies = Column(JSON) #Column(ARRAY(Float))
final_molecule = Column(Integer, ForeignKey('molecule.id'))
final_molecule_obj = relationship(MoleculeORM, lazy='select', foreign_keys=final_molecule)
# ids, calculated not stored in this table
# NOTE: this won't work in SQLite since it returns ARRAYS, aggregate_order_by
trajectory = column_property(
select([func.array_agg(aggregate_order_by(Trajectory.result_id,
Trajectory.position))]).where(Trajectory.opt_id == id))
# array of objects (results) - Lazy - raise error of accessed
trajectory_obj = relationship(Trajectory, cascade="all, delete-orphan",
# backref="optimization_procedure",
order_by=Trajectory.position,
collection_class=ordering_list('position'))
__mapper_args__ = {
'polymorphic_identity': 'optimization_procedure',
# to have separate select when querying BaseResultsORM
'polymorphic_load': 'selectin',
}
__table_args__ = (
Index('ix_optimization_program', 'program'), # todo: needed for procedures?
)
def update_relations(self, trajectory=None, **kwarg):
# update optimization_results relations
# self._update_many_to_many(opt_result_association, 'opt_id', 'result_id',
# self.id, trajectory, self.trajectory)
self.trajectory_obj = []
trajectory = [] if not trajectory else trajectory
for result_id in trajectory:
traj = Trajectory(opt_id=int(self.id), result_id=int(result_id))
self.trajectory_obj.append(traj)
def test_aggregate_order_by_two(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.string_agg(
table.c.a, aggregate_order_by(literal_column("','"), table.c.a))
stmt = select([expr])
self.assert_compile(
stmt, "SELECT string_agg(table1.a, ',' ORDER BY table1.a) "
"AS string_agg_1 FROM table1")
def test_aggregate_order_by_one(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])
# note this tests that the object exports FROM objects
# correctly
self.assert_compile(
stmt, "SELECT array_agg(table1.a ORDER BY table1.b DESC) "
"AS array_agg_1 FROM table1")
def test_aggregate_order_by_adapt(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])
a1 = table.alias('foo')
stmt2 = sql_util.ClauseAdapter(a1).traverse(stmt)
self.assert_compile(
stmt2,
"SELECT array_agg(foo.a ORDER BY foo.b DESC) AS array_agg_1 FROM table1 AS foo"
)
def _array_agg_and_order(
to_select: DbColumn[Y],
order_by: DbColumn[Z],
*,
remove_nulls: bool = False
) -> DbColumn[t.Sequence[Y]]:
res = sqlalchemy.func.array_agg(
aggregate_order_by(to_select, order_by).asc()
)
if remove_nulls:
res = sqlalchemy.func.array_remove(res, sqlalchemy.sql.null())
return res
def test_aggregate_order_by_adapt(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])
a1 = table.alias('foo')
stmt2 = sql_util.ClauseAdapter(a1).traverse(stmt)
self.assert_compile(
stmt2,
"SELECT array_agg(foo.a ORDER BY foo.b DESC) AS array_agg_1 FROM table1 AS foo"
)
def get_vocabualary(only_positive=False):
session = db_session()
query = session.query(
func.string_agg(
SourceData.filtered_words,
aggregate_order_by(literal_column("' '"), SourceData.id)))
if only_positive:
query = query.filter(SourceData.is_negative == False)
query = query.first()
session.close()
return u"%s" % str(query)
def test_aggregate_order_by_one(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])
# note this tests that the object exports FROM objects
# correctly
self.assert_compile(
stmt,
"SELECT array_agg(table1.a ORDER BY table1.b DESC) "
"AS array_agg_1 FROM table1"
)
def budget_deptx(client, dept_id):
user = current_user
if client is None:
client = user.client[0].abbreviation
else:
client = Client.query.filter(Client.abbreviation.ilike(client)).first()
if not current_user.has_role(client.abbreviation.lower()):
abort(403)
from mahercpa.modules.models_views import get_budget_actual, BudgetSpendJson, TransactionDetail, func, and_
td = TransactionDetail
dt_filt = (datetime(2017, 4, 1), datetime(2018, 3, 30))
txn = (db.session.query(
td.budget, td.vendor, td.period,
td.amount).filter(td.client_id == client.id).filter(
td.period.between(dt_filt[0], dt_filt[1]),
td.path_id.any(dept_id)).subquery())
q1 = (db.session.query(
txn.c.budget, txn.c.vendor,
func.json_object_agg(txn.c.period,
txn.c.amount).label('months')).group_by(
txn.c.budget, txn.c.vendor).subquery())
q2 = (db.session.query(
td.budget, td.vendor,
func.json_agg(
aggregate_order_by(
func.json_build_object('date', td.date, 'memo', td.memo, 'id',
td.id, 'txn_num', td.txn_number,
'ref_num', td.ref_num, 'amount',
td.amount),
order_by=td.date)).label('transactions')).filter(
td.client_id == client.id).filter(
td.period.between(dt_filt[0], dt_filt[1]),
td.path_id.any(dept_id)).group_by(
td.budget, td.vendor)).subquery()
spend_obj = (db.session.query(
q1.c.budget, q2.c.vendor,
func.json_build_object('budget', q1.c.budget, 'vendor', q1.c.vendor,
'totals', q1.c.months, 'transactions',
q2.c.transactions).label('json')).join(
q2,
and_(q1.c.budget == q2.c.budget,
q1.c.vendor == q2.c.vendor)).all())
return jsonify([r.json for r in spend_obj])
def test_aggregate_order_by_two(self):
m = MetaData()
table = Table('table1', m, Column('a', Integer), Column('b', Integer))
expr = func.string_agg(
table.c.a,
aggregate_order_by(literal_column("','"), table.c.a)
)
stmt = select([expr])
self.assert_compile(
stmt,
"SELECT string_agg(table1.a, ',' ORDER BY table1.a) "
"AS string_agg_1 FROM table1"
)
def create(self):
user_id = context.form.get('userId')
destination = DBSession.query(Member) \
.filter(Member.id == user_id).one_or_none()
if destination is None:
raise HTTPStatus('611 Member Not Found')
current_user = DBSession.query(Member) \
.filter(Member.reference_id == context.identity.reference_id) \
.one()
is_blocked = DBSession.query(member_block) \
.filter(or_(
and_(
member_block.c.member_id == user_id,
member_block.c.blocked_member_id == current_user.id
),
and_(
member_block.c.member_id == current_user.id,
member_block.c.blocked_member_id == user_id
)
)) \
.count()
if is_blocked:
raise HTTPStatus(
'613 Not Allowed To Create Direct With This Member')
source = Member.current()
cte = select([
TargetMember.target_id.label('direct_id'),
func.array_agg(aggregate_order_by(TargetMember.member_id,
TargetMember.member_id),
type_=ARRAY(Integer)).label('members')
]).group_by(TargetMember.target_id).cte()
direct = DBSession.query(Direct) \
.join(cte, cte.c.direct_id == Direct.id) \
.filter(cte.c.members == sorted([source.id, user_id])) \
.one_or_none()
if direct:
return direct
direct = Direct(members=[source, destination])
return direct
def get_tdEntity(conn: Connection,
td_ids: Iterable[int]) -> list[TdEntityNoReverse]:
mstTreasureDeviceLvJson = (select(
mstTreasureDeviceLv.c.treaureDeviceId,
func.jsonb_agg(
aggregate_order_by(mstTreasureDeviceLv.table_valued(),
mstTreasureDeviceLv.c.lv)).label(
mstTreasureDeviceLv.name),
).where(mstTreasureDeviceLv.c.treaureDeviceId.in_(td_ids)).group_by(
mstTreasureDeviceLv.c.treaureDeviceId).cte())
JOINED_TD_TABLES = (mstTreasureDevice.outerjoin(
mstTreasureDeviceDetail,
mstTreasureDeviceDetail.c.id == mstTreasureDevice.c.id,
).outerjoin(
mstSvtTreasureDevice,
mstSvtTreasureDevice.c.treasureDeviceId == mstTreasureDevice.c.id,
).outerjoin(
mstTreasureDeviceLvJson,
mstTreasureDeviceLvJson.c.treaureDeviceId == mstTreasureDevice.c.id,
))
SELECT_TD_ENTITY = [
mstTreasureDevice.c.id,
func.to_jsonb(mstTreasureDevice.table_valued()).label(
mstTreasureDevice.name),
sql_jsonb_agg(mstTreasureDeviceDetail),
sql_jsonb_agg(mstSvtTreasureDevice),
mstTreasureDeviceLvJson.c.mstTreasureDeviceLv,
]
stmt = (select(*SELECT_TD_ENTITY).select_from(JOINED_TD_TABLES).where(
mstTreasureDevice.c.id.in_(td_ids)).group_by(
mstTreasureDevice.c.id,
mstTreasureDeviceLvJson.c.mstTreasureDeviceLv))
skill_entities = [
TdEntityNoReverse.from_orm(skill)
for skill in conn.execute(stmt).fetchall()
]
order = {skill_id: i for i, skill_id in enumerate(td_ids)}
return sorted(skill_entities,
key=lambda td: order[td.mstTreasureDevice.id])
def get_negative():
session = db_session()
query = session.query(Statement.id.label('statement_id'), func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created)).label('agg_message')).\
filter(Statement.reviewed==True).\
filter(Statement.is_question==Statement.false_assumption).\
filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\
filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\
group_by(Statement.id).\
subquery()
query_results = session.query(query.c.statement_id, query.c.agg_message, func.length(query.c.agg_message).label('len'), TelegramChannel.tags.label('tags')).\
outerjoin(Statement, Statement.id==query.c.statement_id).\
outerjoin(TelegramChannel, TelegramChannel.channel_id==Statement.channel_id).distinct().all()
session.close()
return query_results
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(self):
case_study_query = (
db.session.query(
CaseStudy.supplier_code.label('supplier_code'),
CaseStudy.data['service'].astext.label('domain'),
func.count(CaseStudy.id).label('count')
)
.group_by(CaseStudy.supplier_code, CaseStudy.data['service'].astext)
)
subquery = (
case_study_query
.intersect(
case_study_query
.filter(CaseStudy.status == 'rejected')
)
.subquery()
)
results = (
db
.session
.query(
Supplier.id,
Supplier.code,
Supplier.name,
func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label('domains')
)
.join(SupplierDomain, Domain)
.join(subquery, and_(
Supplier.code == subquery.columns.supplier_code,
Domain.name == subquery.columns.domain
))
.filter(
Supplier.status != 'deleted',
Supplier.data['recruiter'].astext.in_(['no', 'both']),
SupplierDomain.status == 'unassessed'
)
.group_by(Supplier.id, Supplier.code, Supplier.name)
.all()
)
return [r._asdict() for r in results]
def do_auto_review():
session = db_session()
subquery = session.query(Statement.id.label('statement_id'), func.length(func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created))).label('agg_message')).\
filter(Statement.reviewed==False).\
filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\
filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\
group_by(Statement.id).\
subquery()
query = session.query(subquery.c.statement_id).filter(
subquery.c.agg_message < MINIMIM_QUESTION_LENGHT).subquery()
stmt = update(Statement).where(Statement.id.in_(query)).values(
reviewed=True, is_question=False, false_assumption=False)
session.execute(stmt)
session.commit()
session.close()
async def get_sessions_with_cached_data(
session: BacktestSessionInputSchema) -> List[BacktestSessionSchema]:
response: List[BacktestSessionSchema] = []
sources_ids = session.sources_ids
async with db.transaction():
query = (db.select(BacktestSessionModel).select_from(
BacktestSessionModel.join(BacktestSessionSourceModel)).where(
and_(
BacktestSessionModel.cached_session_id == None,
BacktestSessionModel.finished_datetime.isnot(None),
BacktestSessionModel.start_datetime <=
session.start_datetime,
BacktestSessionModel.end_datetime >= session.end_datetime,
)).group_by(BacktestSessionModel.id).having(
db.func.array_agg(
aggregate_order_by(
BacktestSessionSourceModel.source_id,
BacktestSessionSourceModel.source_id.asc())) ==
sources_ids))
async for s in query.gino.iterate():
response.append(BacktestSessionSchema.from_orm(s))
return response
def __init__(self, tables, *, column=None, order=None, label=None,
join_on=None):
self._tables = list(always_iterable(tables))
self._label = label
self._from = reduce(lambda x, y: x.join(y), self._tables)
if self._label is not None:
self._from = self._from.alias(self._label)
if column is not None:
self.column = column
else:
if len(self._tables) > 1:
raise ValueError('For multi-table-joined relations `column` should be specified')
self.column = self._tables[0].c.id
if order is not None:
self._order = order
else:
self._order = self.column.asc().nullslast()
self._join_on = join_on
self.aggregated = array_agg(aggregate_order_by(self.column, self._order))
def get_teams_overview(self, user_id, agency_id):
teams_led_by_user = (
db
.session
.query(TeamMember.team_id)
.join(Team)
.filter(
Team.status == 'completed',
TeamMember.user_id == user_id,
TeamMember.is_team_lead.is_(True)
)
.subquery('teams_led_by_user')
)
team_leads = (
db
.session
.query(
TeamMember.team_id,
func.array_agg(aggregate_order_by(User.name, User.name)).label('leads')
)
.join(User)
.filter(
TeamMember.is_team_lead.is_(True),
User.agency_id == agency_id,
User.role == 'buyer'
)
.group_by(TeamMember.team_id)
.subquery('team_leads')
)
team_members = (
db
.session
.query(
TeamMember.team_id,
func.array_agg(aggregate_order_by(User.name, User.name)).label('members')
)
.join(User)
.filter(
TeamMember.is_team_lead.is_(False),
User.agency_id == agency_id,
User.role == 'buyer'
)
.group_by(TeamMember.team_id)
.subquery('team_members')
)
result = (
db
.session
.query(
Team.id,
Team.name,
team_leads.columns.leads,
team_members.columns.members,
case(
[(teams_led_by_user.columns.team_id.isnot(None), True)],
else_=False
).label('isTeamLead')
)
.join(team_leads, team_leads.columns.team_id == Team.id)
.join(team_members, team_members.columns.team_id == Team.id, isouter=True)
.join(teams_led_by_user, teams_led_by_user.columns.team_id == Team.id, isouter=True)
.filter(Team.status == 'completed')
.order_by(Team.name)
.all()
)
return [r._asdict() for r in result]
def get_taxa_list(id_area):
"""
:param type:
:return:
"""
try:
reproduction_id = (
(
DB.session.query(TNomenclatures.id_nomenclature)
.join(
BibNomenclaturesTypes,
TNomenclatures.id_type == BibNomenclaturesTypes.id_type,
)
.filter(
and_(
BibNomenclaturesTypes.mnemonique.like("STATUT_BIO"),
TNomenclatures.cd_nomenclature.like("3"),
)
)
)
.first()
.id_nomenclature
)
print("reproduction_id", reproduction_id)
query_territory = (
DB.session.query(
Taxref.cd_ref.label("id"),
LAreas.id_area,
LAreas.area_code,
Taxref.cd_ref,
func.split_part(Taxref.nom_vern, ",", 1).label("nom_vern"),
Taxref.nom_valide,
Taxref.group1_inpn,
Taxref.group2_inpn,
func.count(distinct(Synthese.id_synthese)).label("count_occtax"),
func.count(distinct(Synthese.observers)).label("count_observer"),
func.count(distinct(Synthese.date_min)).label("count_date"),
func.count(distinct(Synthese.id_dataset)).label("count_dataset"),
func.max(distinct(func.extract("year", Synthese.date_min))).label(
"last_year"
),
func.array_agg(
aggregate_order_by(
distinct(func.extract("year", Synthese.date_min)),
func.extract("year", Synthese.date_min).desc(),
)
).label("list_years"),
func.array_agg(
aggregate_order_by(
distinct(func.extract("month", Synthese.date_min)),
func.extract("month", Synthese.date_min).asc(),
)
).label("list_months"),
func.bool_or(
Synthese.id_nomenclature_bio_status == reproduction_id
).label("reproduction"),
func.max(distinct(func.extract("year", Synthese.date_min)))
.filter(Synthese.id_nomenclature_bio_status == reproduction_id)
.label("last_year_reproduction"),
func.array_agg(distinct(Synthese.id_nomenclature_bio_status)).label(
"bio_status_id"
),
case(
[(func.count(TaxrefProtectionEspeces.cd_nom) > 0, True)],
else_=False,
).label("protection"),
)
.select_from(CorAreaSynthese)
.join(Synthese, Synthese.id_synthese == CorAreaSynthese.id_synthese)
.join(Taxref, Synthese.cd_nom == Taxref.cd_nom)
.join(LAreas, LAreas.id_area == CorAreaSynthese.id_area)
.outerjoin(TaxrefLR, TaxrefLR.cd_nom == Taxref.cd_ref)
.outerjoin(
TaxrefProtectionEspeces, TaxrefProtectionEspeces.cd_nom == Taxref.cd_nom
)
.filter(LAreas.id_area == id_area)
.group_by(
LAreas.id_area,
LAreas.area_code,
Taxref.cd_ref,
Taxref.nom_vern,
Taxref.nom_valide,
Taxref.group1_inpn,
Taxref.group2_inpn,
)
.order_by(
func.count(distinct(Synthese.id_synthese)).desc(),
Taxref.group1_inpn,
Taxref.group2_inpn,
Taxref.nom_valide,
)
)
print("query_territory", query_territory)
result = query_territory.all()
count = len(result)
data = []
for r in result:
dict = r._asdict()
bio_status = []
for s in r.bio_status_id:
bio_status.append(get_nomenclature(s))
dict["bio_status"] = bio_status
redlist = get_redlist_status(r.cd_ref)
dict["redlist"] = redlist
data.append(dict)
redlistless_data = list(filter(redlist_list_is_null, data))
print("redlistless_data", len(redlistless_data))
redlist_data = list(filter(redlist_is_not_null, data))
print("redlist_data", len(redlist_data))
redlist_sorted_data = sorted(
redlist_data,
key=lambda k: (
k["redlist"][0]["priority_order"],
k["redlist"][0]["threatened"],
),
)
sorted_data = redlist_sorted_data + list(redlistless_data)
return jsonify({"count": count, "data": sorted_data}), 200
except Exception as e:
error = "<get_taxa_list> ERROR: {}".format(e)
current_app.logger.error(error)
return {"Error": error}, 400
def generate_view_selectable():
cte = (
sa.select(
[
sa.literal_column('endpoint_sip.uuid').label('uuid'),
sa.literal(0).label('level'),
sa.literal('0', sa.String).label('path'),
sa.literal_column('endpoint_sip.uuid').label('root'),
]
)
.select_from(sa.table('endpoint_sip'))
.cte(recursive=True)
)
endpoints = cte.union_all(
sa.select(
[
sa.literal_column('endpoint_sip_template.parent_uuid').label('uuid'),
(cte.c.level + 1).label('level'),
(
cte.c.path
+ sa.cast(
func.row_number().over(
partition_by='level',
order_by=sa.literal_column(
'endpoint_sip_template.priority'
),
),
sa.String,
)
).label('path'),
(cte.c.root),
]
).select_from(
sa.join(
cte,
sa.table('endpoint_sip_template'),
cte.c.uuid == sa.literal_column('endpoint_sip_template.child_uuid'),
)
)
)
return (
sa.select(
[
endpoints.c.root,
sa.cast(
sa.func.jsonb_object(
array_agg(
aggregate_order_by(
sa.literal_column('endpoint_sip_section_option.key'),
endpoints.c.path.desc(),
)
),
array_agg(
aggregate_order_by(
sa.literal_column('endpoint_sip_section_option.value'),
endpoints.c.path.desc(),
)
),
),
JSONB,
).label('options'),
]
)
.select_from(
sa.join(
endpoints,
sa.table('endpoint_sip_section'),
sa.literal_column('endpoint_sip_section.endpoint_sip_uuid')
== endpoints.c.uuid,
).join(
sa.table('endpoint_sip_section_option'),
sa.literal_column(
'endpoint_sip_section_option.endpoint_sip_section_uuid'
)
== sa.literal_column('endpoint_sip_section.uuid'),
)
)
.group_by(endpoints.c.root)
)
def ballot_retrieval_list(jurisdiction: Jurisdiction, round: Round) -> str:
previous_ballots = set(
SampledBallotDraw.query.join(Round)
.filter(Round.round_num < round.round_num)
.join(SampledBallot)
.join(Batch)
.filter_by(jurisdiction_id=jurisdiction.id)
.values(Batch.tabulator, Batch.name, SampledBallot.ballot_position)
)
ballots = list(
SampledBallotDraw.query.filter_by(round_id=round.id)
.join(SampledBallot)
.join(Batch)
.filter_by(jurisdiction_id=jurisdiction.id)
.outerjoin(
CvrBallot,
and_(
CvrBallot.batch_id == SampledBallot.batch_id,
CvrBallot.ballot_position == SampledBallot.ballot_position,
),
)
.join(SampledBallot.audit_board)
.group_by(AuditBoard.id, SampledBallot.id, Batch.id, CvrBallot.imprinted_id)
.order_by(
AuditBoard.name,
Batch.container,
Batch.tabulator,
Batch.name,
SampledBallot.ballot_position,
)
.values(
Batch.container,
Batch.tabulator,
Batch.name,
SampledBallot.ballot_position,
CvrBallot.imprinted_id,
func.string_agg(
SampledBallotDraw.ticket_number,
aggregate_order_by(
literal_column("','"), SampledBallotDraw.ticket_number
),
),
AuditBoard.name,
)
)
show_imprinted_id = jurisdiction.election.audit_type == AuditType.BALLOT_COMPARISON
show_container = len(ballots) > 0 and ballots[0][0] is not None
show_tabulator = len(ballots) > 0 and ballots[0][1] is not None
csv_io = io.StringIO()
retrieval_list_writer = csv.writer(csv_io)
columns_to_show = [
("Container", show_container),
("Tabulator", show_tabulator),
("Batch Name", True),
("Ballot Number", True),
("Imprinted ID", show_imprinted_id),
("Ticket Numbers", True),
("Already Audited", True),
("Audit Board", True),
]
retrieval_list_writer.writerow(
[header for header, should_show in columns_to_show if should_show]
)
for ballot in ballots:
(
container,
tabulator,
batch_name,
position,
imprinted_id,
ticket_numbers,
audit_board_name,
) = ballot
previously_audited = (
"Y" if (tabulator, batch_name, position) in previous_ballots else "N"
)
values_to_show = [
(container, show_container),
(tabulator, show_tabulator),
(batch_name, True),
(position, True),
(imprinted_id, show_imprinted_id),
(ticket_numbers, True),
(previously_audited, True),
(audit_board_name, True),
]
retrieval_list_writer.writerow(
[value for value, should_show in values_to_show if should_show]
)
return csv_io.getvalue()
