def restricted_worker_filter(query: orm.Query, current_user: DispatchUser,
role: UserRoles):
"""Adds additional incident type filters to query (usually for permissions)."""
if current_user:
if role == UserRoles.PLANNER:
team_list = [i.id for i in current_user.managed_teams]
team_list.append(current_user.default_team_id)
query = query.filter(Worker.team_id.in_(set(team_list)))
elif role in (UserRoles.WORKER, UserRoles.CUSTOMER):
query = (query.filter(Worker.id == -999999))
query.distinct()
return query
def count_eligible_facts(self, query: Query) -> int:
begin_overall_start = time.time()
facts = query.distinct().count()
overall_end_time = time.time()
overall_total_time = overall_end_time - begin_overall_start
logger.info("overall time count: " + str(overall_total_time))
return facts
def restricted_incident_filter(query: orm.Query, current_user: DispatchUser,
role: UserRoles):
"""Adds additional incident filters to query (usually for permissions)."""
if role == UserRoles.member:
# We filter out resticted incidents for users with a member role if the user is not an incident participant
query = (query.join(
Participant, Incident.id == Participant.incident_id).join(
IndividualContact).filter(
or_(
Incident.visibility == Visibility.open,
IndividualContact.email == current_user.email,
)))
return query.distinct()
def restricted_incident_filter(query: orm.Query, current_user: DispatchUser,
role: UserRoles):
"""Adds additional incident filters to query (usually for permissions)."""
if role != UserRoles.owner:
# We don't allow users that are not owners to see restricted incidents
query = (query.join(
Participant, Incident.id == Participant.incident_id).join(
IndividualContact).filter(
or_(
Incident.visibility == Visibility.open,
IndividualContact.email == current_user.email,
)))
return query.distinct()
def restricted_job_filter(query: orm.Query, current_user: DispatchUser,
role: UserRoles):
"""Adds additional incident type filters to query (usually for permissions)."""
if current_user:
if role == UserRoles.WORKER:
query = (query.join(
Worker, Worker.id == Job.scheduled_primary_worker_id).join(
DispatchUser,
DispatchUser.id == Worker.dispatch_user_id).filter(
DispatchUser.email == current_user.email))
query.distinct()
elif role == UserRoles.PLANNER:
team_list = [i.id for i in current_user.managed_teams]
team_list.append(current_user.default_team_id)
query = query.filter(Job.team_id.in_(set(team_list)))
elif role == UserRoles.CUSTOMER:
# team_list = [i.id for i in current_user.managed_teams]
locs = location_service.get_by_auth_email(db_session=query.session,
email=current_user.email)
loc_id_list = [i.id for i in locs]
query = query.filter(Job.location_id.in_(set(loc_id_list)))
return query
def orm_get_rows(self,
table_name,
filter_text=None,
distinct=False,
delete=False):
"""
Returns all rows from selected columns in a table, provides options to filter your query and return only
distinct values.
Parameters
----------
table_name: str | DeclarativeMeta
The table name you want to search within. Alternatively an ORM model may be passed directly.
filter_text: str | dict
Text that you want to filter source by. Allows a dict of multiple filter texts to be passed.
distinct: bool
True indicates you only want distinct source without duplicates.
delete : bool
True indicates you want to delete all returned rows from the target table.
Returns
----------
source : list
"""
self.session = Session(bind=self.sa_engine)
if type(table_name) == str:
table_model = self.reflect_database_table(table_name=table_name)
else:
table_model = table_name
if filter_text is None:
if distinct:
results = Query(table_model, self.session).distinct().all()
else:
results = Query(table_model, self.session).all()
elif type(filter_text) == dict:
query = Query(table_model, self.session)
for attr, value in filter_text.items():
if value == '':
pass
else:
query = Query(table_model, self.session).filter(
getattr(table_model, attr) == value, )
if distinct:
results = query.distinct().all()
else:
results = query.all()
elif type(filter_text) == BinaryExpression:
if distinct:
results = Query(
table_model,
self.session).filter(filter_text).distinct().all()
else:
results = Query(table_model,
self.session).filter(filter_text).all()
else:
if distinct:
results = Query(
table_model,
self.session).filter(filter_text).distinct().all()
else:
results = Query(table_model,
self.session).filter(filter_text).all()
if delete and len(results) > 0:
print(
f'Attempting to delete {len(results)} from {table_name.name}.')
try:
if filter_text is None:
Query(table_model,
self.session).delete(synchronize_session=False)
else:
Query(table_model,
self.session).filter(filter_text).delete(
synchronize_session=False)
self.session.commit()
return
except Exception as e:
print(
f'ERROR:: The delete operation was unsuccessful, operation aborted.'
)
self.session.rollback()
raise DbObjectError(e)
return results
# entities by default. Use select_from() to specify a table.
Query(User, Address).select_from(User).join(User.addresses)
# left join
Query(User).outerjoin(Address)
# filter and filter_by, can be chained.
query.filter_by(id=1) # uses keyword arguments
query.filter(User.id == 1) # use SQL expression object
query.filter(text("id<10")) # use textual SQL
(query.filter(text("id<:value and name=:name")) # bind parameters
.params(value=10, name='hello'))
# other option
query.order_by(User.id)
query.distinct()
query.limit()
query.offset()
# emit query and get result #################################
# the select Query instance will not emit a query until the
# following method invoked
query.all() # fetch all as list
query.first() # fetch one or None
query.one() # fetch one or NoResultFound or MultipleResultsFound
query.one_or_none() # fetch one or None or MultipleResultsFound
query.scalar() # invokes the one() method and return the first column
result = query[1:3] # list with limit and offset
