def __eq__(self, other): from MaKaC.conference import Category, Conference, Contribution, SubContribution, Session if isinstance(other, Category): return db.and_(self.cls.link_type == LinkType.category, self.cls.category_id == int(other.id)) elif isinstance(other, Conference): return db.and_(self.cls.link_type == LinkType.event, self.cls.event_id == int(other.id)) elif isinstance(other, Session): return db.and_( self.cls.link_type == LinkType.session, self.cls.event_id == int(other.getConference().id), self.cls.session_id == other.id, ) elif isinstance(other, Contribution): return db.and_( self.cls.link_type == LinkType.contribution, self.cls.event_id == int(other.getConference().id), self.cls.contribution_id == other.id, ) elif isinstance(other, SubContribution): return db.and_( self.cls.link_type == LinkType.subcontribution, self.cls.event_id == int(other.getConference().id), self.cls.contribution_id == other.getContribution().id, self.cls.subcontribution_id == other.id, ) else: raise ValueError("Unexpected object type {}: {}".format(type(other), other))
def _filter_by_sessions(self, session_ids, added_since): sid_query = Contribution.session_id.in_(set(session_ids)) session_query = db.and_(AttachmentFolder.link_type == LinkType.session, AttachmentFolder.session.has(Session.id.in_(session_ids) & ~Session.is_deleted)) contrib_query = db.and_(AttachmentFolder.link_type == LinkType.contribution, AttachmentFolder.contribution.has(sid_query & ~Contribution.is_deleted)) subcontrib_query = db.and_(AttachmentFolder.link_type == LinkType.subcontribution, AttachmentFolder.subcontribution.has( sid_query & ~SubContribution.is_deleted & ~Contribution.is_deleted)) return self._build_base_query(added_since).filter(db.or_(session_query, contrib_query, subcontrib_query)).all()
def _filter_list_entries(self, query, filters): criteria = [] field_filters = filters.get('fields') item_filters = filters.get('items') extra_filters = filters.get('extra') if not (field_filters or item_filters or extra_filters): return query if field_filters: for contribution_type_id, field_values in field_filters.iteritems(): criteria.append(Abstract.field_values.any(db.and_( AbstractFieldValue.contribution_field_id == contribution_type_id, AbstractFieldValue.data.op('#>>')('{}').in_(field_values) ))) if item_filters: static_filters = { 'accepted_track': Abstract.accepted_track_id, 'accepted_contrib_type': Abstract.accepted_contrib_type_id, 'submitted_contrib_type': Abstract.submitted_contrib_type_id, 'submitted_for_tracks': Abstract.submitted_for_tracks, 'reviewed_for_tracks': Abstract.reviewed_for_tracks } for key, column in static_filters.iteritems(): ids = set(item_filters.get(key, ())) if not ids: continue column_criteria = [] if '_for_tracks' in key: if None in ids: column_criteria.append(~column.any()) ids.discard(None) if ids: column_criteria.append(column.any(Track.id.in_(ids))) else: if None in ids: column_criteria.append(column.is_(None)) ids.discard(None) if ids: column_criteria.append(column.in_(ids)) criteria.append(db.or_(*column_criteria)) if 'state' in item_filters: states = [AbstractState(int(state)) for state in item_filters['state']] criteria.append(Abstract.state.in_(states)) if extra_filters: if extra_filters.get('multiple_tracks'): submitted_for_count = (db.select([db.func.count()]) .as_scalar() .where(Abstract.submitted_for_tracks.prop.primaryjoin)) criteria.append(submitted_for_count > 1) if extra_filters.get('comments'): criteria.append(Abstract.submission_comment != '') return query.filter(db.and_(*criteria))
def __eq__(self, other): if isinstance(other, db.m.Event): return db.and_(self.cls.link_type == VCRoomLinkType.event, self.cls.linked_event_id == other.id) elif isinstance(other, db.m.SessionBlock): return db.and_(self.cls.link_type == VCRoomLinkType.block, self.cls.session_block_id == other.id) elif isinstance(other, db.m.Contribution): return db.and_(self.cls.link_type == VCRoomLinkType.contribution, self.cls.contribution_id == other.id) else: raise TypeError('Unexpected object type {}: {}'.format(type(other), other))
def get_room_events(room, start_dt, end_dt, repeat_frequency, repeat_interval): occurrences = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval)) excluded_categories = rb_settings.get('excluded_categories') return (Event.query .filter(~Event.is_deleted, Event.own_room == room, db.or_(Event.happens_between(as_utc(occ.start_dt), as_utc(occ.end_dt)) for occ in occurrences), Event.timezone == config.DEFAULT_TIMEZONE, db.and_(Event.category_id != cat['id'] for cat in excluded_categories), Event.acl_entries.any(db.and_(EventPrincipal.type == PrincipalType.user, EventPrincipal.user_id == session.user.id, EventPrincipal.full_access))) .all())
def _query_managed_rooms(user): criteria = [db.and_(RoomPrincipal.type == PrincipalType.user, RoomPrincipal.user_id == user.id, RoomPrincipal.has_management_permission())] for group in user.local_groups: criteria.append(db.and_(RoomPrincipal.type == PrincipalType.local_group, RoomPrincipal.local_group_id == group.id, RoomPrincipal.has_management_permission())) for group in user.iter_all_multipass_groups(): criteria.append(db.and_(RoomPrincipal.type == PrincipalType.multipass_group, RoomPrincipal.multipass_group_provider == group.provider.name, db.func.lower(RoomPrincipal.multipass_group_name) == group.name.lower(), RoomPrincipal.has_management_permission())) return Room.query.filter(~Room.is_deleted, Room.acl_entries.any(db.or_(*criteria)))
def _filter_list_entries(self, query, filters): if not (filters.get('fields') or filters.get('items')): return query field_types = {str(f.id): f.field_impl for f in self.regform.form_items if f.is_field and not f.is_deleted and (f.parent_id is None or not f.parent.is_deleted)} field_filters = {field_id: data_list for field_id, data_list in filters['fields'].iteritems() if field_id in field_types} if not field_filters and not filters['items']: return query criteria = [db.and_(RegistrationFormFieldData.field_id == field_id, field_types[field_id].create_sql_filter(data_list)) for field_id, data_list in field_filters.iteritems()] items_criteria = [] if 'checked_in' in filters['items']: checked_in_values = filters['items']['checked_in'] # If both values 'true' and 'false' are selected, there's no point in filtering if len(checked_in_values) == 1: items_criteria.append(Registration.checked_in == bool(int(checked_in_values[0]))) if 'state' in filters['items']: states = [RegistrationState(int(state)) for state in filters['items']['state']] items_criteria.append(Registration.state.in_(states)) if field_filters: subquery = (RegistrationData.query .with_entities(db.func.count(RegistrationData.registration_id)) .join(RegistrationData.field_data) .filter(RegistrationData.registration_id == Registration.id) .filter(db.or_(*criteria)) .correlate(Registration) .as_scalar()) query = query.filter(subquery == len(field_filters)) return query.filter(db.or_(*items_criteria))
def get_related_categories(user, detailed=True): """Gets the related categories of a user for the dashboard""" favorites = set() if user.favorite_categories: favorites = set(Category.query .filter(Category.id.in_(c.id for c in user.favorite_categories)) .options(undefer('chain_titles')) .all()) managed = set(Category.query .filter(Category.acl_entries.any(db.and_(CategoryPrincipal.type == PrincipalType.user, CategoryPrincipal.user == user, CategoryPrincipal.has_management_role())), ~Category.is_deleted) .options(undefer('chain_titles'))) if not detailed: return favorites | managed res = {} for categ in favorites | managed: res[(categ.title, categ.id)] = { 'categ': categ, 'favorite': categ in favorites, 'managed': categ in managed, 'path': truncate_path(categ.chain_titles[:-1], chars=50) } return OrderedDict(sorted(res.items(), key=itemgetter(0)))
def get_track_reviewer_abstract_counts(event, user): """Get the numbers of abstracts per track for a specific user. Note that this does not take into account if the user is a reviewer for a track; it just checks whether the user has reviewed an abstract in a track or not. :return: A dict mapping tracks to dicts containing the counts. """ # COUNT() does not count NULL values so we pass NULL in case an # abstract is not in the submitted state. That way we still get # the track - filtering using WHERE would only include tracks # that have some abstract in the submitted state. count_total = db.func.count(Abstract.id) count_reviewable = db.func.count(db.case({AbstractState.submitted.value: Abstract.id}, value=Abstract.state)) count_reviewable_reviewed = db.func.count(db.case({AbstractState.submitted.value: AbstractReview.id}, value=Abstract.state)) count_total_reviewed = db.func.count(AbstractReview.id) query = (Track.query.with_parent(event) .with_entities(Track, count_total, count_total_reviewed, count_reviewable - count_reviewable_reviewed) .outerjoin(Track.abstracts_reviewed) .outerjoin(AbstractReview, db.and_(AbstractReview.abstract_id == Abstract.id, AbstractReview.user_id == user.id)) .group_by(Track.id)) return {track: {'total': total, 'reviewed': reviewed, 'unreviewed': unreviewed} for track, total, reviewed, unreviewed in query}
def visibility_horizon_query(self): """Get a query object that returns the highest category this one is visible from.""" cte_query = ( select( [ Category.id, Category.parent_id, db.case([(Category.visibility.is_(None), None)], else_=(Category.visibility - 1)).label("n"), literal(0).label("level"), ] ) .where(Category.id == self.id) .cte("visibility_horizon", recursive=True) ) parent_query = select( [ Category.id, Category.parent_id, db.case( [(Category.visibility.is_(None) & cte_query.c.n.is_(None), None)], else_=db.func.least(Category.visibility, cte_query.c.n) - 1, ), cte_query.c.level + 1, ] ).where(db.and_(Category.id == cte_query.c.parent_id, (cte_query.c.n > 0) | cte_query.c.n.is_(None))) cte_query = cte_query.union_all(parent_query) return db.session.query(cte_query.c.id, cte_query.c.n).order_by(cte_query.c.level.desc()).limit(1)
def _get_persons(event, condition): """Queries event persons linked to contributions in the event, filtered using the condition provided.""" return (event.persons.filter(EventPerson.contribution_links.any( db.and_(condition, ContributionPersonLink.contribution.has(~Contribution.is_deleted)))) .options(joinedload('contribution_links').joinedload('contribution')) .order_by(db.func.lower(EventPerson.last_name)))
def get_events_with_abstract_persons(user, dt=None): """ Return a dict of event ids and the abstract submission related roles the user has in that event. :param user: A `User` :param dt: Only include events taking place on/after that date """ data = defaultdict(set) bad_states = {AbstractState.withdrawn, AbstractState.rejected} # submitter query = (Abstract.query .filter(~Event.is_deleted, ~Abstract.is_deleted, ~Abstract.state.in_(bad_states), Event.ends_after(dt), Abstract.submitter == user) .join(Abstract.event_new) .options(load_only('event_id'))) for abstract in query: data[abstract.event_id].add('abstract_submitter') # person abstract_criterion = db.and_(~Abstract.state.in_(bad_states), ~Abstract.is_deleted) query = (user.event_persons .filter(~Event.is_deleted, Event.ends_after(dt), EventPerson.abstract_links.any(AbstractPersonLink.abstract.has(abstract_criterion))) .join(EventPerson.event_new) .options(load_only('event_id'))) for person in query: data[person.event_id].add('abstract_person') return data
def get_matching_events(start_dt, end_dt, repeat_frequency, repeat_interval): """Get events suitable for booking linking. This finds events that overlap with an occurrence of a booking with the given dates where the user is a manager. """ occurrences = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval)) excluded_categories = rb_settings.get('excluded_categories') return (Event.query .filter(~Event.is_deleted, ~Event.room_reservation_links.any(ReservationLink.reservation.has(Reservation.is_accepted)), db.or_(Event.happens_between(as_utc(occ.start_dt), as_utc(occ.end_dt)) for occ in occurrences), Event.timezone == config.DEFAULT_TIMEZONE, db.and_(Event.category_id != cat.id for cat in excluded_categories), Event.acl_entries.any(db.and_(EventPrincipal.type == PrincipalType.user, EventPrincipal.user_id == session.user.id, EventPrincipal.full_access))) .all())
def __eq__(self, other): if isinstance(other, db.m.Category): return db.and_(self.cls.link_type == LinkType.category, self.cls.category_id == other.id) elif isinstance(other, db.m.Event): return db.and_(self.cls.link_type == LinkType.event, self.cls.linked_event_id == other.id) elif isinstance(other, db.m.Session): return db.and_(self.cls.link_type == LinkType.session, self.cls.session_id == other.id) elif isinstance(other, db.m.Contribution): return db.and_(self.cls.link_type == LinkType.contribution, self.cls.contribution_id == other.id) elif isinstance(other, db.m.SubContribution): return db.and_(self.cls.link_type == LinkType.subcontribution, self.cls.subcontribution_id == other.id) else: raise ValueError('Unexpected object type {}: {}'.format(type(other), other))
def is_submission_in_progress(survey): """Check whether the current user has a survey submission in progress""" from indico.modules.events.surveys.models.surveys import Survey if session.user: query = (Survey.query.with_parent(survey.event) .filter(Survey.submissions.any(db.and_(~SurveySubmission.is_submitted, SurveySubmission.user == session.user)))) user_incomplete_surveys = set(query) return survey in user_incomplete_surveys else: return False
def attachment_count(cls): from indico.modules.attachments.models.attachments import Attachment from indico.modules.attachments.models.folders import AttachmentFolder query = (db.select([db.func.count(Attachment.id)]) .select_from(db.join(Attachment, AttachmentFolder, Attachment.folder_id == AttachmentFolder.id)) .where(db.and_( ~AttachmentFolder.is_deleted, ~Attachment.is_deleted, (getattr(AttachmentFolder, cls.ATTACHMENT_FOLDER_ID_COLUMN) == cls.id) )) .correlate_except(AttachmentFolder, Attachment)) return db.column_property(query, deferred=True)
def visible_categories_cte(self): """ Get a sqlalchemy select for the visible categories within this category, including the category itself. """ cte_query = (select([Category.id, literal(0).label('level')]) .where((Category.id == self.id) & (Category.visibility.is_(None) | (Category.visibility > 0))) .cte('visibility_chain', recursive=True)) parent_query = (select([Category.id, cte_query.c.level + 1]) .where(db.and_(Category.parent_id == cte_query.c.id, db.or_(Category.visibility.is_(None), Category.visibility > cte_query.c.level + 1)))) return cte_query.union_all(parent_query)
def _paper_last_revision(cls): # Incompatible with joinedload subquery = (db.select([db.func.max(PaperRevision.submitted_dt)]) .where(PaperRevision._contribution_id == cls.id) .correlate_except(PaperRevision) .as_scalar()) return db.relationship( 'PaperRevision', uselist=False, lazy=True, viewonly=True, primaryjoin=db.and_(PaperRevision._contribution_id == cls.id, PaperRevision.submitted_dt == subquery) )
def was_survey_submitted(survey): """Check whether the current user has submitted a survey""" from indico.modules.events.surveys.models.surveys import Survey query = (Survey.query.with_parent(survey.event) .filter(Survey.submissions.any(db.and_(SurveySubmission.is_submitted, SurveySubmission.user == session.user)))) user_submitted_surveys = set(query) if session.user and survey in user_submitted_surveys: return True submission_id = session.get('submitted_surveys', {}).get(survey.id) if submission_id is None: return False return SurveySubmission.find(id=submission_id, is_submitted=True).has_rows()
def get_linked_object(type_, id_): if type_ == LinkType.event: return Event.get(id_, is_deleted=False) elif type_ == LinkType.contribution: return (Contribution.query .filter(Contribution.id == id_, ~Contribution.is_deleted, Contribution.event.has(is_deleted=False)) .first()) elif type_ == LinkType.session_block: return (SessionBlock.query .filter(SessionBlock.id == id_, SessionBlock.session.has(db.and_(~Session.is_deleted, Session.event.has(is_deleted=False)))) .first())
def add_contrib_data(): has_contrib = (EventPerson.contribution_links.any( ContributionPersonLink.contribution.has(~Contribution.is_deleted))) has_subcontrib = EventPerson.subcontribution_links.any( SubContributionPersonLink.subcontribution.has(db.and_( ~SubContribution.is_deleted, SubContribution.contribution.has(~Contribution.is_deleted)))) query = (Event.query .options(load_only('id')) .options(noload('*')) .filter(~Event.is_deleted, Event.ends_after(dt), Event.persons.any((EventPerson.user_id == user.id) & (has_contrib | has_subcontrib)))) for event in query: data[event.id].add('contributor')
def get_active_bookings(limit, start_dt, last_reservation_id=None, **filters): criteria = [ReservationOccurrence.start_dt > start_dt] if last_reservation_id is not None: criteria.append(db.and_(db.cast(ReservationOccurrence.start_dt, db.Date) >= start_dt, ReservationOccurrence.reservation_id > last_reservation_id)) query = (_bookings_query(filters) .filter(db.or_(*criteria)) .order_by(ReservationOccurrence.start_dt, ReservationOccurrence.reservation_id, db.func.indico.natsort(Room.full_name)) .limit(limit)) bookings, total = with_total_rows(query) rows_left = total - limit if total > limit else total return group_by_occurrence_date(query, sort_by=lambda obj: (obj.start_dt, obj.reservation_id)), rows_left
def get_room_blockings(timeframe=None, created_by=None, in_rooms_owned_by=None): query = (Blocking.query .join(Blocking.blocked_rooms) .join(BlockedRoom.room) .options(contains_eager('blocked_rooms').contains_eager('room'), selectinload('_allowed'))) criteria = [] if timeframe == 'recent': criteria.append(Blocking.end_date >= date.today()) elif timeframe == 'year': criteria.extend([Blocking.start_date <= date(date.today().year, 12, 31), Blocking.end_date >= date(date.today().year, 1, 1)]) if created_by: criteria.append(Blocking.created_by_user == created_by) if in_rooms_owned_by: criteria.append(BlockedRoom.room_id.in_(get_managed_room_ids(in_rooms_owned_by))) query = query.filter(db.and_(*criteria)) return query.all()
def category_suggestions(): users = (User.query .filter(~User.is_deleted, User._all_settings.any(db.and_(UserSetting.module == 'users', UserSetting.name == 'suggest_categories', db.cast(UserSetting.value, db.String) == 'true')))) for user in users: existing = {x.category: x for x in user.suggested_categories} related = set(get_related_categories(user, detailed=False)) for category, score in get_category_scores(user).iteritems(): if score < SUGGESTION_MIN_SCORE: continue if (category in related or category.is_deleted or category.suggestions_disabled or any(p.suggestions_disabled for p in category.parent_chain_query)): continue logger.debug('Suggesting %s with score %.03f for %s', category, score, user) suggestion = existing.get(category) or SuggestedCategory(category=category, user=user) suggestion.score = score user.settings.set('suggest_categories', False) db.session.commit()
def build_user_search_query(criteria, exact=False, include_deleted=False, include_pending=False, favorites_first=False): unspecified = object() query = User.query.distinct(User.id).options(db.joinedload(User._all_emails)) if not include_pending: query = query.filter(~User.is_pending) if not include_deleted: query = query.filter(~User.is_deleted) affiliation = criteria.pop('affiliation', unspecified) if affiliation is not unspecified: query = query.join(UserAffiliation).filter(unaccent_match(UserAffiliation.name, affiliation, exact)) email = criteria.pop('email', unspecified) if email is not unspecified: query = query.join(UserEmail).filter(unaccent_match(UserEmail.email, email, exact)) # search on any of the name fields (first_name OR last_name) name = criteria.pop('name', unspecified) if name is not unspecified: if exact: raise ValueError("'name' is not compatible with 'exact'") if 'first_name' in criteria or 'last_name' in criteria: raise ValueError("'name' is not compatible with (first|last)_name") query = query.filter(_build_name_search(name.replace(',', '').split())) for k, v in criteria.iteritems(): query = query.filter(unaccent_match(getattr(User, k), v, exact)) # wrap as subquery so we can apply order regardless of distinct-by-id query = query.from_self() if favorites_first: query = (query.outerjoin(favorite_user_table, db.and_(favorite_user_table.c.user_id == session.user.id, favorite_user_table.c.target_id == User.id)) .order_by(nullslast(favorite_user_table.c.user_id))) query = query.order_by(db.func.lower(db.func.indico.indico_unaccent(User.first_name)), db.func.lower(db.func.indico.indico_unaccent(User.last_name)), User.id) return query
def get_members(self): from indico.modules.users.models.users import User if self.group is None: warn('Tried to get members for invalid group {}'.format(self)) return set() # We actually care about Users, not identities here! emails = set() identifiers = set() for identity_info in self.group: identifiers.add(identity_info.identifier) emails |= {x.lower() for x in identity_info.data.getlist('email') if x} if not identifiers and not emails: return set() return set(User.query.outerjoin(Identity).filter( ~User.is_deleted, db.or_( User.all_emails.contains(db.func.any(list(emails))), db.and_( Identity.provider == self.provider, Identity.identifier.in_(identifiers) ) )))
def _filter_registration(regform, query, filters): if not filters["fields"] and not filters["items"]: return query field_types = { f.id: f.field_impl for f in regform.form_items if f.is_field and not f.is_deleted and (f.parent_id is None or not f.parent.is_deleted) } criteria = [ db.and_(RegistrationFormFieldData.field_id == field_id, field_types[field_id].create_sql_filter(data_list)) for field_id, data_list in filters["fields"].iteritems() ] items_criteria = [] if "checked_in" in filters["items"]: checked_in_values = filters["items"]["checked_in"] # If both values 'true' and 'false' are selected, there's no point in filtering if len(checked_in_values) == 1: items_criteria.append(Registration.checked_in == bool(int(checked_in_values[0]))) if "state" in filters["items"]: states = [RegistrationState(int(state)) for state in filters["items"]["state"]] items_criteria.append(Registration.state.in_(states)) if filters["fields"]: subquery = ( RegistrationData.query.with_entities(db.func.count(RegistrationData.registration_id)) .join(RegistrationData.field_data) .filter(RegistrationData.registration_id == Registration.id) .filter(db.or_(*criteria)) .correlate(Registration) .as_scalar() ) query = query.filter(subquery == len(filters["fields"])) return query.filter(db.or_(*items_criteria))
def _process_cascaded_category_contents(records): """ Travel from categories to subcontributions, flattening the whole event structure. Yields everything that it finds (except for elements whose protection has changed but are not inheriting their protection settings from anywhere). :param records: queue records to process """ category_prot_records = {rec.category_id for rec in records if rec.type == EntryType.category and rec.change == ChangeType.protection_changed} category_move_records = {rec.category_id for rec in records if rec.type == EntryType.category and rec.change == ChangeType.moved} changed_events = set() category_prot_records -= category_move_records # A move already implies sending the whole record # Protection changes are handled differently, as there may not be the need to re-generate the record if category_prot_records: for categ in Category.find(Category.id.in_(category_prot_records)): cte = categ.get_protection_parent_cte() # Update only children that inherit inheriting_categ_children = (Event.query .join(cte, db.and_((Event.category_id == cte.c.id), (cte.c.protection_parent == categ.id)))) inheriting_direct_children = Event.find((Event.category_id == categ.id) & Event.is_inheriting) changed_events.update(itertools.chain(inheriting_direct_children, inheriting_categ_children)) # Add move operations and explicitly-passed event records if category_move_records: changed_events.update(Event.find(Event.category_chain_overlaps(category_move_records))) for elem in _process_cascaded_event_contents(records, additional_events=changed_events): yield elem
def get_event_regforms(event, user, with_registrations=False): """Get registration forms with information about user registrations. :param event: the `Event` to get registration forms for :param user: A `User` :param with_registrations: Whether to return the user's registration instead of just whether they have one """ if not user: registered_user = db.literal(None if with_registrations else False) elif with_registrations: registered_user = Registration else: registered_user = RegistrationForm.registrations.any((Registration.user == user) & Registration.is_active) query = (RegistrationForm.query.with_parent(event) .with_entities(RegistrationForm, registered_user) .options(undefer('active_registration_count')) .order_by(db.func.lower(RegistrationForm.title))) if with_registrations: query = query.outerjoin(Registration, db.and_(Registration.registration_form_id == RegistrationForm.id, Registration.user == user, Registration.is_active)) return query.all()
def query_notes(ids=None): contrib_event = db.aliased(Event) contrib_session = db.aliased(Session) subcontrib_contrib = db.aliased(Contribution) subcontrib_session = db.aliased(Session) subcontrib_event = db.aliased(Event) session_event = db.aliased(Event) note_strategy = load_only('id', 'link_type', 'event_id', 'linked_event_id', 'contribution_id', 'subcontribution_id', 'session_id', 'html') # event apply_acl_entry_strategy( note_strategy.contains_eager(EventNote.linked_event).selectinload( Event.acl_entries), EventPrincipal) # contribution contrib_strategy = note_strategy.contains_eager(EventNote.contribution) apply_acl_entry_strategy( contrib_strategy.selectinload(Contribution.acl_entries), ContributionPrincipal) apply_acl_entry_strategy( contrib_strategy.contains_eager( Contribution.event.of_type(contrib_event)).selectinload( contrib_event.acl_entries), EventPrincipal) apply_acl_entry_strategy( contrib_strategy.contains_eager( Contribution.session.of_type(contrib_session)).selectinload( contrib_session.acl_entries), SessionPrincipal) # subcontribution subcontrib_strategy = note_strategy.contains_eager( EventNote.subcontribution) subcontrib_contrib_strategy = subcontrib_strategy.contains_eager( SubContribution.contribution.of_type(subcontrib_contrib)) apply_acl_entry_strategy( subcontrib_contrib_strategy.selectinload( subcontrib_contrib.acl_entries), ContributionPrincipal) apply_acl_entry_strategy( subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.event.of_type(subcontrib_event)).selectinload( subcontrib_event.acl_entries), EventPrincipal) apply_acl_entry_strategy( subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.session.of_type( subcontrib_session)).selectinload( subcontrib_session.acl_entries), SessionPrincipal) # session session_strategy = note_strategy.contains_eager(EventNote.session) session_strategy.contains_eager( Session.event.of_type(session_event)).selectinload( session_event.acl_entries) apply_acl_entry_strategy( session_strategy.selectinload(Session.acl_entries), SessionPrincipal) if ids is None: export_filter = db.and_( ~EventNote.is_deleted, db.or_(EventNote.link_type != LinkType.event, ~Event.is_deleted & _get_excluded_category_filter()), db.or_( EventNote.link_type != LinkType.contribution, ~Contribution.is_deleted & ~contrib_event.is_deleted & _get_excluded_category_filter(contrib_event)), db.or_( EventNote.link_type != LinkType.subcontribution, db.and_(~SubContribution.is_deleted, ~subcontrib_contrib.is_deleted, ~subcontrib_event.is_deleted, _get_excluded_category_filter(subcontrib_event))), db.or_( EventNote.link_type != LinkType.session, ~Session.is_deleted & ~session_event.is_deleted & _get_excluded_category_filter(session_event))) else: export_filter = EventNote.id.in_(ids) return (EventNote.query.outerjoin(EventNote.linked_event).outerjoin( EventNote.contribution ).outerjoin(Contribution.event.of_type(contrib_event)).outerjoin( Contribution.session.of_type(contrib_session) ).outerjoin(EventNote.subcontribution).outerjoin( SubContribution.contribution.of_type(subcontrib_contrib)).outerjoin( subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin( subcontrib_contrib.session.of_type(subcontrib_session)). outerjoin(EventNote.session).outerjoin( Session.event.of_type(session_event)).filter( export_filter).options( note_strategy, joinedload(EventNote.current_revision).joinedload( EventNoteRevision.user).joinedload('_affiliation'), ).order_by(EventNote.id))
def search_for_rooms(filters, only_available=False): query = ( Room.query.outerjoin( favorite_room_table, db.and_( favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)).reset_joinpoint( ) # otherwise filter_by() would apply to the favorite table .options(raiseload('owner')).filter(Room.is_active).order_by( favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter( db.or_(Room.capacity >= (filters['capacity'] * 0.8), Room.capacity.is_(None))) if 'building' in filters: criteria['building'] = filters['building'] if 'floor' in filters: criteria['floor'] = filters['floor'] query = query.filter_by(**criteria) if 'text' in filters: query = query.filter(_make_room_text_filter(filters['text'])) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation).with_entities( db.func.count(RoomEquipmentAssociation.c.room_id)).filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment'])).join( EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id).correlate(Room).as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) if ids: query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if not only_available: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) query = query.filter( Room.filter_available(start_dt, end_dt, repeatability, include_pre_bookings=True, include_pending_blockings=True)) if not rb_is_admin(session.user): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) own_rooms = [r.id for r in Room.get_owned_by(session.user)] query = query.filter( db.or_( Room.id.in_(own_rooms) if own_rooms else False, db.and_( Room.filter_bookable_hours(start_dt.time(), end_dt.time()), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)))) return query
def get_non_inheriting_objects(root): """Get a set of child objects that do not inherit protection. :param root: An event object (`Event`, `Session`, `Contribution` or `AttachmentFolder`) which may contain objects with a different protection. """ def _query_folders(obj, crit): return (db.m.AttachmentFolder.query.filter_by( event=obj.event, is_deleted=False).filter(crit).options(joinedload('attachments'))) def _process_attachments(folders): for folder in folders: if not folder.is_inheriting: yield folder for attachment in folder.attachments: if not attachment.is_inheriting: yield attachment if isinstance(root, db.m.Event): # For an event we check sessions, contributions and ALL attachments no matter where for sess in db.m.Session.query.with_parent(root).filter( ~db.m.Session.is_inheriting): yield _ProtectedObjectWrapper(sess) for contrib in db.m.Contribution.query.with_parent(root).filter( ~db.m.Contribution.is_inheriting): yield _ProtectedObjectWrapper(contrib) query = (root.all_attachment_folders.filter_by( is_deleted=False).options(joinedload('attachments'))) for obj in _process_attachments(query): yield _ProtectedObjectWrapper(obj) elif isinstance(root, db.m.Session): # For a session we check contributions and attachments within the session crit = db.or_( # attached to the session db.m.AttachmentFolder.object == root, # attached to a contribution in the session db.and_( db.m.AttachmentFolder.link_type == LinkType.contribution, db.m.AttachmentFolder.contribution.has( db.and_(db.m.Contribution.session == root, ~db.m.Contribution.is_deleted))), # attached to a subcontribution in a contribution in the session db.and_( db.m.AttachmentFolder.link_type == LinkType.subcontribution, db.m.AttachmentFolder.subcontribution.has( db.and_( ~db.m.SubContribution.is_deleted, db.m.SubContribution.contribution.has( db.and_(db.m.Contribution.session == root, ~db.m.Contribution.is_deleted)))))) for obj in _process_attachments(_query_folders(root, crit)): yield _ProtectedObjectWrapper(obj) for contrib in root.contributions: if not contrib.is_inheriting: yield _ProtectedObjectWrapper(contrib) elif isinstance(root, db.m.Contribution): # For a contribution we check attachments and subcontrib attachments crit = db.or_( # attached to the contribution db.m.AttachmentFolder.object == root, # attached to a subcontribution of the contribution db.and_( db.m.AttachmentFolder.link_type == LinkType.subcontribution, db.m.AttachmentFolder.subcontribution.has( db.and_(db.m.SubContribution.contribution == root, ~db.m.SubContribution.is_deleted)))) for obj in _process_attachments(_query_folders(root, crit)): yield _ProtectedObjectWrapper(obj) elif isinstance(root, db.m.AttachmentFolder): # For an attachment folder we only check attachments in there for attachment in root.attachments: if not attachment.is_inheriting: yield _ProtectedObjectWrapper(attachment) else: raise TypeError('Unexpected object of type {}: {}'.format( type(root).__name__, root))
def _query_sessions_for_user(event, user): return (Session.query.with_parent(event).filter( Session.acl_entries.any( db.and_(SessionPrincipal.has_management_permission('coordinate'), SessionPrincipal.user == user))))
def get_persons(self): abstract_strategy = joinedload('abstract_links') abstract_strategy.joinedload('abstract') abstract_strategy.joinedload('person').joinedload('user') contribution_strategy = joinedload('contribution_links') contribution_strategy.joinedload('contribution') contribution_strategy.joinedload('person').joinedload('user') subcontribution_strategy = joinedload('subcontribution_links') subcontribution_strategy.joinedload('subcontribution') subcontribution_strategy.joinedload('person').joinedload('user') session_block_strategy = joinedload('session_block_links') session_block_strategy.joinedload('session_block') session_block_strategy.joinedload('person').joinedload('user') event_strategy = joinedload('event_links') event_strategy.joinedload('person').joinedload('user') chairpersons = {link.person for link in self.event.person_links} persons = defaultdict( lambda: { 'roles': OrderedDict(), 'registrations': [], 'has_event_person': True, 'id_field_name': 'person_id' }) _reg_person_join = db.or_( (EventPerson.user_id == Registration.user_id), db.and_(EventPerson.user_id.is_(None), Registration.user_id.is_(None), EventPerson.email == Registration.email)) event_persons_query = (db.session.query( EventPerson, Registration).filter( EventPerson.event_id == self.event.id).outerjoin( Registration, (Registration.event_id == self.event.id) & _reg_person_join).options(abstract_strategy, event_strategy, contribution_strategy, subcontribution_strategy, session_block_strategy).all()) event_user_roles = defaultdict(set) for event_role in self.event.roles: for user in event_role.members: event_user_roles[user].add(event_role) event_person_users = set() for event_person, registration in event_persons_query: data = persons[event_person.email or event_person.id] if registration: data['registrations'].append(registration) data['person'] = event_person if event_person in chairpersons: data['roles']['chairperson'] = BUILTIN_ROLES[ 'chairperson'].copy() if self.event.type == 'lecture': continue if self.event.has_feature('abstracts'): abstracts = { person_link.abstract_id: self.generate_abstracts_data(person_link) for person_link in event_person.abstract_links if not person_link.abstract.is_deleted } if abstracts: data['roles']['author'] = BUILTIN_ROLES['author'].copy() data['roles']['author']['elements'] = abstracts session_blocks = { person_link.session_block_id: self.generate_sessions_data(person_link) for person_link in event_person.session_block_links if not person_link.session_block.session.is_deleted } if session_blocks: data['roles']['convener'] = BUILTIN_ROLES['convener'].copy() data['roles']['convener']['elements'] = session_blocks contributions = { person_link.contribution.id: self.generate_contributions_data(person_link) for person_link in event_person.contribution_links if person_link.is_speaker and not person_link.contribution.is_deleted } subcontributions = { person_link.subcontribution.id: self.generate_subcontributions_data(person_link) for person_link in event_person.subcontribution_links if not person_link.subcontribution.is_deleted and not person_link.subcontribution.contribution.is_deleted } if contributions or subcontributions: data['roles']['speaker'] = BUILTIN_ROLES['speaker'].copy() data['roles']['speaker']['elements'] = dict( contributions, **subcontributions) event_user_roles_data = {} for role in event_user_roles[event_person.user]: event_user_roles_data['custom_{}'.format(role.id)] = { 'name': role.name, 'code': role.code, 'css': role.css } event_user_roles_data = OrderedDict( sorted(event_user_roles_data.items(), key=lambda t: t[1]['code'])) data['roles'] = OrderedDict(data['roles'].items() + event_user_roles_data.items()) event_person_users.add(event_person.user) internal_role_users = defaultdict( lambda: { 'roles': OrderedDict(), 'person': [], 'has_event_person': False, 'id_field_name': 'user_id' }) for user, roles in event_user_roles.viewitems(): if user in event_person_users: continue for role in roles: user_metadata = internal_role_users[user.email] user_metadata['person'] = user user_metadata['roles']['custom_{}'.format(role.id)] = { 'name': role.name, 'code': role.code, 'css': role.css } user_metadata['roles'] = OrderedDict( sorted(user_metadata['roles'].items(), key=lambda x: x[1]['code'])) # Some EventPersons will have no roles since they were connected to deleted things persons = { email: data for email, data in persons.viewitems() if any(data['roles'].viewvalues()) } persons = dict(persons, **internal_role_users) return persons
def _filter_list_entries(self, query, filters): criteria = [] field_filters = filters.get('fields') item_filters = filters.get('items') extra_filters = filters.get('extra') if not (field_filters or item_filters or extra_filters): return query if field_filters: for contribution_type_id, field_values in field_filters.iteritems( ): criteria.append( Abstract.field_values.any( db.and_( AbstractFieldValue.contribution_field_id == contribution_type_id, AbstractFieldValue.data.op('#>>')('{}').in_( field_values)))) if item_filters: static_filters = { 'accepted_track': Abstract.accepted_track_id, 'accepted_contrib_type': Abstract.accepted_contrib_type_id, 'submitted_contrib_type': Abstract.submitted_contrib_type_id, 'submitted_for_tracks': Abstract.submitted_for_tracks, 'reviewed_for_tracks': Abstract.reviewed_for_tracks } for key, column in static_filters.iteritems(): ids = set(item_filters.get(key, ())) if not ids: continue column_criteria = [] if '_for_tracks' in key: if None in ids: column_criteria.append(~column.any()) ids.discard(None) if ids: column_criteria.append(column.any(Track.id.in_(ids))) else: if None in ids: column_criteria.append(column.is_(None)) ids.discard(None) if ids: column_criteria.append(column.in_(ids)) criteria.append(db.or_(*column_criteria)) if 'state' in item_filters: states = [ AbstractState(int(state)) for state in item_filters['state'] ] criteria.append(Abstract.state.in_(states)) if extra_filters: if extra_filters.get('multiple_tracks'): submitted_for_count = (db.select( [db.func.count()]).as_scalar().where( Abstract.submitted_for_tracks.prop.primaryjoin)) criteria.append(submitted_for_count > 1) if extra_filters.get('comments'): criteria.append(Abstract.submission_comment != '') return query.filter(db.and_(*criteria))
def _process_cascaded_event_contents(records, additional_events=None, *, include_deleted=False, skip_all_deleted=False): """ Flatten a series of records into its most basic elements (subcontribution level). Yields results. :param records: queue records to process :param additional_events: events whose content will be included in addition to those found in records :param include_deleted: whether to include soft-deleted objects as well :param skip_all_deleted: whether to skip soft-deleted objects even if explicitly queued """ changed_events = additional_events or set() changed_sessions = set() changed_contributions = set() changed_subcontributions = set() changed_attachments = set() changed_notes = set() def _deleted_cond(cond): return True if include_deleted else cond def _check_deleted(rec): return not skip_all_deleted or not rec.object.is_deleted note_records = { rec.note_id for rec in records if rec.type == EntryType.note and _check_deleted(rec) } attachment_records = { rec.attachment_id for rec in records if rec.type == EntryType.attachment and _check_deleted(rec) } session_records = { rec.session_id for rec in records if rec.type == EntryType.session and _check_deleted(rec) } contribution_records = { rec.contrib_id for rec in records if rec.type == EntryType.contribution and _check_deleted(rec) } subcontribution_records = { rec.subcontrib_id for rec in records if rec.type == EntryType.subcontribution and _check_deleted(rec) } event_records = { rec.event_id for rec in records if rec.type == EntryType.event and _check_deleted(rec) } if attachment_records: changed_attachments.update( Attachment.query.filter(Attachment.id.in_(attachment_records))) if note_records: changed_notes.update( EventNote.query.filter(EventNote.id.in_(note_records))) if event_records: changed_events.update(Event.query.filter(Event.id.in_(event_records))) changed_event_ids = {ev.id for ev in changed_events} if changed_event_ids: changed_attachments.update( Attachment.query.filter( _deleted_cond(~Attachment.is_deleted), Attachment.folder.has( db.and_( AttachmentFolder.linked_event_id.in_( changed_event_ids), _deleted_cond(~AttachmentFolder.is_deleted))))) changed_notes.update( EventNote.query.filter( EventNote.linked_event_id.in_(changed_event_ids), _deleted_cond(~EventNote.is_deleted))) yield from changed_events # Sessions are added (implictly + explicitly changed) if changed_event_ids or session_records: condition = Session.event_id.in_(changed_event_ids) & _deleted_cond( ~Session.is_deleted) if session_records: condition = db.or_(condition, Session.id.in_(session_records)) changed_sessions.update( Session.query.filter(Session.event_id.in_(changed_event_ids), _deleted_cond(~Session.is_deleted))) if changed_sessions: # XXX I kept this very similar to the structure of the code for contributions below, # but why aren't we just merging this into the block right above?! changed_session_ids = {s.id for s in changed_sessions} changed_contributions.update( Contribution.query.filter( Contribution.session_id.in_(changed_session_ids), _deleted_cond(~Contribution.is_deleted))) changed_attachments.update( Attachment.query.filter( _deleted_cond(~Attachment.is_deleted), Attachment.folder.has( db.and_( AttachmentFolder.session_id.in_(changed_session_ids), _deleted_cond(~AttachmentFolder.is_deleted))))) changed_notes.update( EventNote.query.filter( EventNote.session_id.in_(changed_session_ids), _deleted_cond(~EventNote.is_deleted))) # Contributions are added (implictly + explicitly changed) if changed_event_ids or contribution_records: condition = Contribution.event_id.in_( changed_event_ids) & _deleted_cond(~Contribution.is_deleted) if contribution_records: condition = db.or_(condition, Contribution.id.in_(contribution_records)) changed_contributions.update( Contribution.query.filter(condition).options( joinedload('subcontributions'))) for contribution in changed_contributions: yield contribution changed_subcontributions.update(contribution.subcontributions) if changed_contributions: changed_contribution_ids = {c.id for c in changed_contributions} changed_attachments.update( Attachment.query.filter( _deleted_cond(~Attachment.is_deleted), Attachment.folder.has( db.and_( AttachmentFolder.contribution_id.in_( changed_contribution_ids), _deleted_cond(~AttachmentFolder.is_deleted))))) changed_notes.update( EventNote.query.filter( EventNote.contribution_id.in_(changed_contribution_ids), _deleted_cond(~EventNote.is_deleted))) # Same for subcontributions if subcontribution_records: changed_subcontributions.update( SubContribution.query.filter( SubContribution.id.in_(subcontribution_records))) if changed_subcontributions: changed_subcontribution_ids = { sc.id for sc in changed_subcontributions } changed_attachments.update( Attachment.query.filter( _deleted_cond(~Attachment.is_deleted), Attachment.folder.has( db.and_( AttachmentFolder.subcontribution_id.in_( changed_subcontribution_ids), _deleted_cond(~AttachmentFolder.is_deleted))))) changed_notes.update( EventNote.query.filter( EventNote.subcontribution_id.in_(changed_subcontribution_ids), _deleted_cond(~EventNote.is_deleted))) yield from changed_subcontributions yield from changed_attachments yield from changed_notes
def _process_cascaded_category_contents(records): """ Travel from categories to subcontributions, flattening the whole event structure. Yields everything that it finds (except for elements whose protection has changed but are not inheriting their protection settings from anywhere). :param records: queue records to process """ excluded_categories = get_excluded_categories(deep=True) excluded_categories_filter = Event.category_id.notin_( excluded_categories) if excluded_categories else True category_prot_records = { rec.category_id for rec in records if rec.type == EntryType.category and rec.change == ChangeType.protection_changed } category_move_records = { rec.category_id for rec in records if rec.type == EntryType.category and rec.change == ChangeType.moved } category_publishing_records = { rec.category_id for rec in records if rec.type == EntryType.category and rec.change in ( ChangeType.published, ChangeType.unpublished) } changed_events = set() category_prot_records -= category_move_records # A move already implies sending the whole record category_prot_records -= category_publishing_records # A publish/unpublish already implies sending the whole record # Protection changes are handled differently, as there may not be the need to re-generate the record if category_prot_records: for categ in Category.query.filter( Category.id.in_(category_prot_records)): cte = categ.get_protection_parent_cte() # Update only children that inherit inheriting_categ_children = (Event.query.filter( ~Event.is_deleted, excluded_categories_filter).join( cte, db.and_((Event.category_id == cte.c.id), (cte.c.protection_parent == categ.id)))) inheriting_direct_children = Event.query.filter( Event.category_id == categ.id, Event.is_inheriting, ~Event.is_deleted, excluded_categories_filter) changed_events.update( itertools.chain(inheriting_direct_children, inheriting_categ_children)) # Add move operations and explicitly-passed event records if category_move_records: changed_events.update( Event.query.filter( Event.category_chain_overlaps(category_move_records), ~Event.is_deleted, excluded_categories_filter)) if category_publishing_records: changed_events.update( Event.query.filter( Event.category_chain_overlaps(category_publishing_records), ~Event.is_deleted, excluded_categories_filter)) yield from _process_cascaded_event_contents( records, additional_events=changed_events)
def get_persons(self): abstract_strategy = joinedload('abstract_links') abstract_strategy.joinedload('abstract') abstract_strategy.joinedload('person').joinedload('user') contribution_strategy = joinedload('contribution_links') contribution_strategy.joinedload('contribution') contribution_strategy.joinedload('person').joinedload('user') subcontribution_strategy = joinedload('subcontribution_links') subcontribution_strategy.joinedload('subcontribution') subcontribution_strategy.joinedload('person').joinedload('user') session_block_strategy = joinedload('session_block_links') session_block_strategy.joinedload('session_block') session_block_strategy.joinedload('person').joinedload('user') event_strategy = joinedload('event_links') event_strategy.joinedload('person').joinedload('user') chairpersons = {link.person for link in self.event.person_links} persons = defaultdict( lambda: { 'roles': {}, 'registrations': [], 'has_event_person': True, 'id_field_name': 'person_id' }) _reg_person_join = db.or_( (EventPerson.user_id == Registration.user_id), db.and_(EventPerson.user_id.is_(None), Registration.user_id.is_(None), EventPerson.email == Registration.email)) event_persons_query = (db.session.query( EventPerson, Registration).filter( EventPerson.event_id == self.event.id).outerjoin( Registration, (Registration.event_id == self.event.id) & _reg_person_join).options(abstract_strategy, event_strategy, contribution_strategy, subcontribution_strategy, session_block_strategy).all()) event_user_roles = defaultdict(set) for event_role in self.event.roles: for user in event_role.members: event_user_roles[user].add(event_role) event_person_users = set() for event_person, registration in event_persons_query: data = persons[event_person.email or event_person.id] if registration and registration.is_active: data['registrations'].append(registration) data['person'] = event_person if event_person in chairpersons: if self.event.type != 'lecture': data['roles']['chairperson'] = BUILTIN_ROLES[ 'chairperson'].copy() else: data['roles']['lecture_speaker'] = BUILTIN_ROLES[ 'lecture_speaker'].copy() if self.event.type == 'lecture': continue if self.event.has_feature('abstracts'): abstracts = { person_link.abstract_id: self.generate_abstracts_data(person_link) for person_link in event_person.abstract_links if not person_link.abstract.is_deleted } if abstracts: data['roles']['author'] = BUILTIN_ROLES['author'].copy() data['roles']['author']['elements'] = abstracts session_blocks = { person_link.session_block_id: self.generate_sessions_data(person_link) for person_link in event_person.session_block_links if not person_link.session_block.session.is_deleted } if session_blocks: data['roles']['convener'] = BUILTIN_ROLES['convener'].copy() data['roles']['convener']['elements'] = session_blocks contributions = { person_link.contribution.id: self.generate_contributions_data(person_link) for person_link in event_person.contribution_links if person_link.is_speaker and not person_link.contribution.is_deleted } subcontributions = { person_link.subcontribution.id: self.generate_subcontributions_data(person_link) for person_link in event_person.subcontribution_links if not person_link.subcontribution.is_deleted and not person_link.subcontribution.contribution.is_deleted } if contributions or subcontributions: data['roles']['speaker'] = BUILTIN_ROLES['speaker'].copy() data['roles']['speaker'][ 'elements'] = contributions | subcontributions event_user_roles_data = {} for role in event_user_roles[event_person.user]: event_user_roles_data[f'custom_{role.id}'] = { 'name': role.name, 'code': role.code, 'css': role.css } event_user_roles_data = dict( sorted(event_user_roles_data.items(), key=lambda t: t[1]['code'])) data['roles'] = data['roles'] | event_user_roles_data event_person_users.add(event_person.user) internal_role_users = defaultdict( lambda: { 'roles': {}, 'person': [], 'registrations': [], 'has_event_person': False, 'id_field_name': 'user_id' }) for user, roles in event_user_roles.items(): if user in event_person_users: continue for role in roles: user_metadata = internal_role_users[user.email] user_metadata['person'] = user user_metadata['roles'][f'custom_{role.id}'] = { 'name': role.name, 'code': role.code, 'css': role.css } user_metadata['roles'] = dict( sorted(user_metadata['roles'].items(), key=lambda x: x[1]['code'])) regs = (Registration.query.with_parent(self.event).filter( Registration.user_id.in_(data['person'].id for data in internal_role_users.values()), Registration.is_active).all()) for reg in regs: internal_role_users[reg.user.email]['registrations'].append(reg) persons = persons | internal_role_users # Some EventPersons will have no built-in roles since they were connected to deleted things builtin_roles = set(BUILTIN_ROLES) for person in persons: roles = set(persons[person]['roles'].keys()) if not roles: persons[person]['roles']['no_roles'] = True if not roles & builtin_roles: persons[person]['roles']['no_builtin_roles'] = True return persons
def _mappers_configured(): # We create some column properties here since even with `declared_attr` # the code runs at import time, making it impossible/risky to import other # modules or reference the object itself in there. # The advantage of those column properties is that they behave like regular # (read-only) columns even though they are generated by subqueries. This # allows them to be loaded together with the rest of the data, avoiding # extra queries. To load them automatically you need to undefer them using # the `undefer` query option, e.g. `.options(undefer('chain_titles'))`. from indico.modules.events import Event # Category.effective_protection_mode -- the effective protection mode # (public/protected) of the category, even if it's inheriting it from its # parent category cte = Category.get_protection_cte() query = select([cte.c.protection_mode ]).where(cte.c.id == Category.id).correlate_except(cte) Category.effective_protection_mode = column_property(query, deferred=True, expire_on_flush=False) # Category.effective_icon_data -- the effective icon data of the category, # either set on the category itself or inherited from it cte = Category.get_icon_data_cte() query = (select([ db.func.json_build_object('source_id', cte.c.source_id, 'metadata', cte.c.icon_metadata) ]).where(cte.c.id == Category.id).correlate_except(cte)) Category.effective_icon_data = column_property(query, deferred=True) # Category.event_count -- the number of events in the category itself, # excluding deleted events query = (select([db.func.count(Event.id) ]).where((Event.category_id == Category.id) & ~Event.is_deleted).correlate_except(Event)) Category.event_count = column_property(query, deferred=True) # Category.has_events -- whether the category itself contains any # events, excluding deleted events query = (exists([1]).where((Event.category_id == Category.id) & ~Event.is_deleted).correlate_except(Event)) Category.has_events = column_property(query, deferred=True) # Category.chain_titles -- a list of the titles in the parent chain, # starting with the root category down to the current category. cte = Category.get_tree_cte('title') query = select([cte.c.path ]).where(cte.c.id == Category.id).correlate_except(cte) Category.chain_titles = column_property(query, deferred=True) # Category.chain -- a list of the ids and titles in the parent # chain, starting with the root category down to the current # category. Each chain entry is a dict containing 'id' and `title`. cte = Category.get_tree_cte(lambda cat: db.func.json_build_object( 'id', cat.id, 'title', cat.title)) query = select([cte.c.path ]).where(cte.c.id == Category.id).correlate_except(cte) Category.chain = column_property(query, deferred=True) # Category.deep_events_count -- the number of events in the category # or any child category (excluding deleted events) cte = Category.get_tree_cte() crit = db.and_(cte.c.id == Event.category_id, cte.c.path.contains(array([Category.id])), ~cte.c.is_deleted, ~Event.is_deleted) query = select([db.func.count()]).where(crit).correlate_except(Event) Category.deep_events_count = column_property(query, deferred=True) # Category.deep_children_count -- the number of subcategories in the # category or any child category (excluding deleted ones) cte = Category.get_tree_cte() crit = db.and_(cte.c.path.contains(array([Category.id])), cte.c.id != Category.id, ~cte.c.is_deleted) query = select([db.func.count()]).where(crit).correlate_except(cte) Category.deep_children_count = column_property(query, deferred=True)
def query_attachments(ids=None): contrib_event = db.aliased(Event) contrib_session = db.aliased(Session) subcontrib_contrib = db.aliased(Contribution) subcontrib_session = db.aliased(Session) subcontrib_event = db.aliased(Event) session_event = db.aliased(Event) attachment_strategy = apply_acl_entry_strategy( selectinload(Attachment.acl_entries), AttachmentPrincipal) folder_strategy = contains_eager(Attachment.folder) folder_strategy.load_only('id', 'protection_mode', 'link_type', 'category_id', 'event_id', 'linked_event_id', 'contribution_id', 'subcontribution_id', 'session_id') apply_acl_entry_strategy( folder_strategy.selectinload(AttachmentFolder.acl_entries), AttachmentFolderPrincipal) # event apply_acl_entry_strategy( folder_strategy.contains_eager( AttachmentFolder.linked_event).selectinload(Event.acl_entries), EventPrincipal) # contribution contrib_strategy = folder_strategy.contains_eager( AttachmentFolder.contribution) apply_acl_entry_strategy( contrib_strategy.selectinload(Contribution.acl_entries), ContributionPrincipal) apply_acl_entry_strategy( contrib_strategy.contains_eager( Contribution.event.of_type(contrib_event)).selectinload( contrib_event.acl_entries), EventPrincipal) apply_acl_entry_strategy( contrib_strategy.contains_eager( Contribution.session.of_type(contrib_session)).selectinload( contrib_session.acl_entries), SessionPrincipal) # subcontribution subcontrib_strategy = folder_strategy.contains_eager( AttachmentFolder.subcontribution) subcontrib_contrib_strategy = subcontrib_strategy.contains_eager( SubContribution.contribution.of_type(subcontrib_contrib)) apply_acl_entry_strategy( subcontrib_contrib_strategy.selectinload( subcontrib_contrib.acl_entries), ContributionPrincipal) apply_acl_entry_strategy( subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.event.of_type(subcontrib_event)).selectinload( subcontrib_event.acl_entries), EventPrincipal) apply_acl_entry_strategy( subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.session.of_type( subcontrib_session)).selectinload( subcontrib_session.acl_entries), SessionPrincipal) # session session_strategy = folder_strategy.contains_eager(AttachmentFolder.session) session_strategy.contains_eager( Session.event.of_type(session_event)).selectinload( session_event.acl_entries) apply_acl_entry_strategy( session_strategy.selectinload(Session.acl_entries), SessionPrincipal) if ids is None: export_filter = db.and_( ~Attachment.is_deleted, ~AttachmentFolder.is_deleted, db.or_( AttachmentFolder.link_type != LinkType.event, ~Event.is_deleted & _get_excluded_category_filter(), ), db.or_( AttachmentFolder.link_type != LinkType.contribution, ~Contribution.is_deleted & ~contrib_event.is_deleted & _get_excluded_category_filter(contrib_event)), db.or_( AttachmentFolder.link_type != LinkType.subcontribution, db.and_(~SubContribution.is_deleted, ~subcontrib_contrib.is_deleted, ~subcontrib_event.is_deleted, _get_excluded_category_filter(subcontrib_event))), db.or_( AttachmentFolder.link_type != LinkType.session, ~Session.is_deleted & ~session_event.is_deleted & _get_excluded_category_filter(session_event))) else: export_filter = Attachment.id.in_(ids) return ( Attachment.query.join(Attachment.folder).options( folder_strategy, attachment_strategy, joinedload(Attachment.user).joinedload('_affiliation')).outerjoin( AttachmentFolder.linked_event).outerjoin( AttachmentFolder.contribution).outerjoin( Contribution.event.of_type(contrib_event)).outerjoin( Contribution.session.of_type(contrib_session)). outerjoin(AttachmentFolder.subcontribution).outerjoin( SubContribution.contribution.of_type(subcontrib_contrib) ).outerjoin( subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin( subcontrib_contrib.session.of_type(subcontrib_session)). outerjoin(AttachmentFolder.session).outerjoin( Session.event.of_type(session_event)).filter(export_filter).filter( AttachmentFolder.link_type != LinkType.category).order_by( Attachment.id))
def search_attachments(self, q, user, page, category_id, event_id, admin_override_enabled): contrib_event = db.aliased(Event) contrib_session = db.aliased(Session) subcontrib_contrib = db.aliased(Contribution) subcontrib_session = db.aliased(Session) subcontrib_event = db.aliased(Event) session_event = db.aliased(Event) attachment_strategy = _apply_acl_entry_strategy( selectinload(Attachment.acl_entries), AttachmentPrincipal) folder_strategy = contains_eager(Attachment.folder) folder_strategy.load_only('id', 'protection_mode', 'link_type', 'category_id', 'event_id', 'linked_event_id', 'contribution_id', 'subcontribution_id', 'session_id') _apply_acl_entry_strategy( folder_strategy.selectinload(AttachmentFolder.acl_entries), AttachmentFolderPrincipal) # event event_strategy = folder_strategy.contains_eager( AttachmentFolder.linked_event) _apply_event_access_strategy(event_strategy) _apply_acl_entry_strategy( event_strategy.selectinload(Event.acl_entries), EventPrincipal) # contribution contrib_strategy = folder_strategy.contains_eager( AttachmentFolder.contribution) _apply_contrib_access_strategy(contrib_strategy) _apply_acl_entry_strategy( contrib_strategy.selectinload(Contribution.acl_entries), ContributionPrincipal) contrib_event_strategy = contrib_strategy.contains_eager( Contribution.event.of_type(contrib_event)) _apply_event_access_strategy(contrib_event_strategy) _apply_acl_entry_strategy( contrib_event_strategy.selectinload(contrib_event.acl_entries), EventPrincipal) contrib_session_strategy = contrib_strategy.contains_eager( Contribution.session.of_type(contrib_session)) contrib_session_strategy.load_only('id', 'event_id', 'protection_mode') _apply_acl_entry_strategy( contrib_session_strategy.selectinload(contrib_session.acl_entries), SessionPrincipal) # subcontribution subcontrib_strategy = folder_strategy.contains_eager( AttachmentFolder.subcontribution) subcontrib_strategy.load_only('id', 'contribution_id', 'title') subcontrib_contrib_strategy = subcontrib_strategy.contains_eager( SubContribution.contribution.of_type(subcontrib_contrib)) _apply_contrib_access_strategy(subcontrib_contrib_strategy) _apply_acl_entry_strategy( subcontrib_contrib_strategy.selectinload( subcontrib_contrib.acl_entries), ContributionPrincipal) subcontrib_event_strategy = subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.event.of_type(subcontrib_event)) _apply_event_access_strategy(subcontrib_event_strategy) _apply_acl_entry_strategy( subcontrib_event_strategy.selectinload( subcontrib_event.acl_entries), EventPrincipal) subcontrib_session_strategy = subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.session.of_type(subcontrib_session)) subcontrib_session_strategy.load_only('id', 'event_id', 'protection_mode') _apply_acl_entry_strategy( subcontrib_session_strategy.selectinload( subcontrib_session.acl_entries), SessionPrincipal) # session session_strategy = folder_strategy.contains_eager( AttachmentFolder.session) session_strategy.load_only('id', 'event_id', 'protection_mode') session_event_strategy = session_strategy.contains_eager( Session.event.of_type(session_event)) _apply_event_access_strategy(session_event_strategy) session_event_strategy.selectinload(session_event.acl_entries) _apply_acl_entry_strategy( session_strategy.selectinload(Session.acl_entries), SessionPrincipal) attachment_filters = [ Attachment.title_matches(q), ~Attachment.is_deleted, ~AttachmentFolder.is_deleted, AttachmentFolder.link_type != LinkType.category, db.or_( AttachmentFolder.link_type != LinkType.event, ~Event.is_deleted, ), db.or_(AttachmentFolder.link_type != LinkType.contribution, ~Contribution.is_deleted & ~contrib_event.is_deleted), db.or_( AttachmentFolder.link_type != LinkType.subcontribution, db.and_( ~SubContribution.is_deleted, ~subcontrib_contrib.is_deleted, ~subcontrib_event.is_deleted, )), db.or_(AttachmentFolder.link_type != LinkType.session, ~Session.is_deleted & ~session_event.is_deleted) ] if category_id is not None: attachment_filters.append( AttachmentFolder.event.has( Event.category_chain_overlaps(category_id))) if event_id is not None: attachment_filters.append(AttachmentFolder.event_id == event_id) query = ( Attachment.query.join( Attachment.folder).filter(*attachment_filters).options( folder_strategy, attachment_strategy, joinedload( Attachment.user).joinedload('_affiliation')).outerjoin( AttachmentFolder.linked_event).outerjoin( AttachmentFolder.contribution).outerjoin( Contribution.event.of_type(contrib_event)). outerjoin(Contribution.session.of_type(contrib_session)).outerjoin( AttachmentFolder.subcontribution).outerjoin( SubContribution.contribution.of_type(subcontrib_contrib)). outerjoin( subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin( subcontrib_contrib.session.of_type( subcontrib_session)).outerjoin( AttachmentFolder.session).outerjoin( Session.event.of_type(session_event))) objs, pagenav = self._paginate(query, page, Attachment.id, user, admin_override_enabled) query = (Attachment.query.filter(Attachment.id.in_( a.id for a in objs)).options( joinedload(Attachment.folder).options( joinedload(AttachmentFolder.subcontribution), joinedload(AttachmentFolder.event).options( undefer(Event.detailed_category_chain))))) attachments_by_id = {a.id: a for a in query} attachments = [attachments_by_id[a.id] for a in objs] res = AttachmentSchema(many=True).dump(attachments) return pagenav, AttachmentResultSchema(many=True).load(res)
def search_for_rooms(filters, allow_admin=False, availability=None): """Search for a room, using the provided filters. :param filters: The filters, provided as a dictionary :param allow_admin: A boolean specifying whether admins have override privileges :param availability: A boolean specifying whether (un)available rooms should be provided, or `None` in case all rooms should be returned. """ query = ( Room.query.outerjoin( favorite_room_table, db.and_( favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)).reset_joinpoint( ) # otherwise filter_by() would apply to the favorite table .options(raiseload('owner')).filter(Room.is_active).order_by( favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(Room.capacity >= filters['capacity']) if 'building' in filters: criteria['building'] = filters['building'] if 'division' in filters: criteria['division'] = filters['division'] query = query.filter_by(**criteria) if 'text' in filters: query = query.filter(_make_room_text_filter(filters['text'])) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation).with_entities( db.func.count(RoomEquipmentAssociation.c.room_id)).filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment'])).join( EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id).correlate(Room).as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('features'): for feature in filters['features']: query = query.filter( Room.available_equipment.any( EquipmentType.features.any(RoomFeature.name == feature))) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if availability is None: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) include_blockings = not rb_is_admin(session.user) availability_filters = [ Room.filter_available(start_dt, end_dt, repeatability, include_blockings=include_blockings, include_pre_bookings=True) ] if not (allow_admin and rb_is_admin(session.user)): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) criterion = db.and_( Room.filter_bookable_hours(start_dt.time(), end_dt.time()), Room.filter_nonbookable_periods(start_dt, end_dt), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)) unbookable_ids = [ room.id for room in query.filter(db.and_( *availability_filters), ~criterion) if not room.can_override(session.user, allow_admin=False) ] availability_filters.append(~Room.id.in_(unbookable_ids)) availability_criterion = db.and_(*availability_filters) if availability is False: availability_criterion = ~availability_criterion return query.filter(availability_criterion)
def _query_contributions_with_user_as_submitter(event, user): return (Contribution.query.with_parent(event) .filter(Contribution.acl_entries.any(db.and_(ContributionPrincipal.has_management_role('submit'), ContributionPrincipal.user == user))))
def search_notes(self, q, user, page, category_id, event_id, admin_override_enabled): contrib_event = db.aliased(Event) contrib_session = db.aliased(Session) subcontrib_contrib = db.aliased(Contribution) subcontrib_session = db.aliased(Session) subcontrib_event = db.aliased(Event) session_event = db.aliased(Event) note_strategy = load_only('id', 'link_type', 'event_id', 'linked_event_id', 'contribution_id', 'subcontribution_id', 'session_id', 'html') # event event_strategy = note_strategy.contains_eager(EventNote.linked_event) event_strategy.undefer(Event.effective_protection_mode) _apply_event_access_strategy(event_strategy) _apply_acl_entry_strategy( event_strategy.selectinload(Event.acl_entries), EventPrincipal) # contribution contrib_strategy = note_strategy.contains_eager(EventNote.contribution) _apply_contrib_access_strategy(contrib_strategy) _apply_acl_entry_strategy( contrib_strategy.selectinload(Contribution.acl_entries), ContributionPrincipal) contrib_event_strategy = contrib_strategy.contains_eager( Contribution.event.of_type(contrib_event)) _apply_event_access_strategy(contrib_event_strategy) _apply_acl_entry_strategy( contrib_event_strategy.selectinload(contrib_event.acl_entries), EventPrincipal) contrib_session_strategy = contrib_strategy.contains_eager( Contribution.session.of_type(contrib_session)) contrib_session_strategy.load_only('id', 'event_id', 'protection_mode') _apply_acl_entry_strategy( contrib_session_strategy.selectinload(contrib_session.acl_entries), SessionPrincipal) # subcontribution subcontrib_strategy = note_strategy.contains_eager( EventNote.subcontribution) subcontrib_contrib_strategy = subcontrib_strategy.contains_eager( SubContribution.contribution.of_type(subcontrib_contrib)) _apply_contrib_access_strategy(subcontrib_contrib_strategy) _apply_acl_entry_strategy( subcontrib_contrib_strategy.selectinload( subcontrib_contrib.acl_entries), ContributionPrincipal) subcontrib_event_strategy = subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.event.of_type(subcontrib_event)) _apply_event_access_strategy(subcontrib_event_strategy) _apply_acl_entry_strategy( subcontrib_event_strategy.selectinload( subcontrib_event.acl_entries), EventPrincipal) subcontrib_session_strategy = subcontrib_contrib_strategy.contains_eager( subcontrib_contrib.session.of_type(subcontrib_session)) subcontrib_session_strategy.load_only('id', 'event_id', 'protection_mode') _apply_acl_entry_strategy( subcontrib_session_strategy.selectinload( subcontrib_session.acl_entries), SessionPrincipal) # session session_strategy = note_strategy.contains_eager(EventNote.session) session_strategy.load_only('id', 'event_id', 'protection_mode') session_event_strategy = session_strategy.contains_eager( Session.event.of_type(session_event)) _apply_event_access_strategy(session_event_strategy) session_event_strategy.selectinload(session_event.acl_entries) _apply_acl_entry_strategy( session_strategy.selectinload(Session.acl_entries), SessionPrincipal) note_filters = [ EventNote.html_matches(q), ~EventNote.is_deleted, db.or_(EventNote.link_type != LinkType.event, ~Event.is_deleted), db.or_(EventNote.link_type != LinkType.contribution, ~Contribution.is_deleted & ~contrib_event.is_deleted), db.or_( EventNote.link_type != LinkType.subcontribution, db.and_(~SubContribution.is_deleted, ~subcontrib_contrib.is_deleted, ~subcontrib_event.is_deleted)), db.or_(EventNote.link_type != LinkType.session, ~Session.is_deleted & ~session_event.is_deleted) ] if category_id is not None: note_filters.append( EventNote.event.has( Event.category_chain_overlaps(category_id))) if event_id is not None: note_filters.append(EventNote.event_id == event_id) query = ( EventNote.query.filter( *note_filters).options(note_strategy).outerjoin( EventNote.linked_event).outerjoin( EventNote.contribution).outerjoin( Contribution.event.of_type(contrib_event)). outerjoin(Contribution.session.of_type(contrib_session)).outerjoin( EventNote.subcontribution).outerjoin( SubContribution.contribution.of_type(subcontrib_contrib)). outerjoin( subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin( subcontrib_contrib.session.of_type( subcontrib_session)).outerjoin( EventNote.session).outerjoin( Session.event.of_type(session_event))) objs, pagenav = self._paginate(query, page, EventNote.id, user, admin_override_enabled) query = (EventNote.query.filter(EventNote.id.in_( n.id for n in objs)).options( joinedload(EventNote.contribution), joinedload(EventNote.subcontribution).joinedload( SubContribution.contribution), joinedload(EventNote.event).options( undefer(Event.detailed_category_chain)), joinedload(EventNote.current_revision).joinedload( EventNoteRevision.user).joinedload('_affiliation'), )) notes_by_id = {n.id: n for n in query} notes = [notes_by_id[n.id] for n in objs] res = HTMLStrippingEventNoteSchema(many=True).dump(notes) return pagenav, EventNoteResultSchema(many=True).load(res)
def _query_contributions_with_user_as_submitter(event, user): return (Contribution.query.with_parent(event) .filter(Contribution.acl_entries.any(db.and_(ContributionPrincipal.has_management_permission('submit'), ContributionPrincipal.user == user))))
def _filter_list_entries(self, query, filters): criteria = [] field_filters = filters.get('fields') item_filters = filters.get('items') extra_filters = filters.get('extra') if not (field_filters or item_filters or extra_filters): return query if field_filters: for field_id, field_values in field_filters.items(): field_values = set(field_values) # Support filtering by 'No selection' in single-choice abstract fields. field_criteria = [] if None in field_values: # Handle the case when there is no value in # 'Abstract.field_values' matching the 'field_id'. # This can happen when custom fields are added after the # abstract had already been submitted or when submitting as a regular # user who cannot see a field that is only editable by managers. # In these cases, we still want to show the abstracts. field_values.discard(None) field_criteria += [ ~Abstract.field_values.any( AbstractFieldValue.contribution_field_id == field_id), Abstract.field_values.any( db.and_( AbstractFieldValue.contribution_field_id == field_id, AbstractFieldValue.data.op('#>>')('{}').is_( None))) ] if field_values: field_criteria.append( Abstract.field_values.any( db.and_( AbstractFieldValue.contribution_field_id == field_id, AbstractFieldValue.data.op('#>>')('{}').in_( field_values)))) criteria.append(db.or_(*field_criteria)) if item_filters: static_filters = { 'accepted_track': Abstract.accepted_track_id, 'accepted_contrib_type': Abstract.accepted_contrib_type_id, 'submitted_contrib_type': Abstract.submitted_contrib_type_id, 'submitted_for_tracks': Abstract.submitted_for_tracks, 'reviewed_for_tracks': Abstract.reviewed_for_tracks } for key, column in static_filters.items(): ids = set(item_filters.get(key, ())) if not ids: continue column_criteria = [] if '_for_tracks' in key: if None in ids: column_criteria.append(~column.any()) ids.discard(None) if ids: column_criteria.append(column.any(Track.id.in_(ids))) else: if None in ids: column_criteria.append(column.is_(None)) ids.discard(None) if ids: column_criteria.append(column.in_(ids)) criteria.append(db.or_(*column_criteria)) if 'state' in item_filters: states = [ AbstractState(int(state)) for state in item_filters['state'] ] criteria.append(Abstract.state.in_(states)) if extra_filters: if extra_filters.get('multiple_tracks'): submitted_for_count = (db.select( [db.func.count()]).scalar_subquery().where( Abstract.submitted_for_tracks.prop.primaryjoin)) criteria.append(submitted_for_count > 1) if extra_filters.get('comments'): criteria.append(Abstract.submission_comment != '') return query.filter(db.and_(*criteria))