def query_subcontributions(ids=None): contrib_event = db.aliased(Event) contrib_session = db.aliased(Session) contrib_block = db.aliased(SessionBlock) contrib_strategy = contains_eager(SubContribution.contribution) contrib_strategy.joinedload(Contribution.own_venue) contrib_strategy.joinedload(Contribution.own_room).options( raiseload('*'), joinedload('location')) contrib_strategy.joinedload(Contribution.timetable_entry) apply_acl_entry_strategy( contrib_strategy.selectinload(Contribution.acl_entries), ContributionPrincipal) event_strategy = contrib_strategy.contains_eager( Contribution.event.of_type(contrib_event)) event_strategy.joinedload(contrib_event.own_venue) event_strategy.joinedload(contrib_event.own_room).options( raiseload('*'), joinedload('location')) apply_acl_entry_strategy( event_strategy.selectinload(contrib_event.acl_entries), EventPrincipal) session_strategy = contrib_strategy.contains_eager( Contribution.session.of_type(contrib_session)) apply_acl_entry_strategy( session_strategy.selectinload(contrib_session.acl_entries), SessionPrincipal) session_strategy.joinedload(contrib_session.own_venue) session_strategy.joinedload(contrib_session.own_room).options( raiseload('*'), joinedload('location')) session_block_strategy = contrib_strategy.contains_eager( Contribution.session_block.of_type(contrib_block)) session_block_strategy.joinedload(contrib_block.own_venue) session_block_strategy.joinedload(contrib_block.own_room).options( raiseload('*'), joinedload('location')) if ids is None: export_filter = db.and_(~SubContribution.is_deleted, ~Contribution.is_deleted, ~contrib_event.is_deleted, _get_excluded_category_filter(contrib_event)) else: export_filter = SubContribution.id.in_(ids) return (SubContribution.query.join(Contribution).join( Contribution.event.of_type(contrib_event)).outerjoin( Contribution.session.of_type(contrib_session)).outerjoin( Contribution.session_block.of_type(contrib_block)). filter(export_filter).options( selectinload(SubContribution.person_links).joinedload( 'person').joinedload('user').load_only('is_system'), contrib_strategy, event_strategy, session_strategy, session_block_strategy).order_by(SubContribution.id))
def get_tree_cte(cls, col='id'): """Create a CTE for the category tree. The CTE contains the followign columns: - ``id`` -- the category id - ``path`` -- an array containing the path from the root to the category itself - ``is_deleted`` -- whether the category is deleted :param col: The name of the column to use in the path or a callable receiving the category alias that must return the expression used for the 'path' retrieved by the CTE. """ cat_alias = db.aliased(cls) if callable(col): path_column = col(cat_alias) else: path_column = getattr(cat_alias, col) cte_query = (select([cat_alias.id, array([path_column]).label('path'), cat_alias.is_deleted]) .where(cat_alias.parent_id.is_(None)) .cte(recursive=True)) rec_query = (select([cat_alias.id, cte_query.c.path.op('||')(path_column), cte_query.c.is_deleted | cat_alias.is_deleted]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def get_tree_cte(cls, col='id'): """Create a CTE for the category tree. The CTE contains the following columns: - ``id`` -- the category id - ``path`` -- an array containing the path from the root to the category itself - ``is_deleted`` -- whether the category is deleted :param col: The name of the column to use in the path or a callable receiving the category alias that must return the expression used for the 'path' retrieved by the CTE. """ cat_alias = db.aliased(cls) if callable(col): path_column = col(cat_alias) else: path_column = getattr(cat_alias, col) cte_query = (select([ cat_alias.id, array([path_column]).label('path'), cat_alias.is_deleted ]).where(cat_alias.parent_id.is_(None)).cte(recursive=True)) rec_query = (select([ cat_alias.id, cte_query.c.path.op('||')(path_column), cte_query.c.is_deleted | cat_alias.is_deleted ]).where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def _associate_registrations(user, **kwargs): from indico.modules.events.registration.models.registrations import Registration reg_alias = db.aliased(Registration) subquery = db.session.query(reg_alias).filter(reg_alias.user_id == user.id, reg_alias.registration_form_id == Registration.registration_form_id, ~reg_alias.is_deleted) registrations = (Registration .find(Registration.user_id == None, # noqa Registration.email.in_(user.all_emails), ~subquery.exists(), ~Registration.is_deleted) .order_by(Registration.submitted_dt.desc()) .all()) if not registrations: return done = set() for registration in registrations: if registration.registration_form_id in done: continue logger.info('Associating %s with %s', registration, user) registration.user = user done.add(registration.registration_form_id) db.session.flush() num = len(done) flash(ngettext("A registration has been linked to your account.", "{n} registrations have been linked to your account.", num).format(n=num), 'info')
def _associate_registrations(user, **kwargs): from indico.modules.events.registration.models.registrations import Registration reg_alias = db.aliased(Registration) subquery = db.session.query(reg_alias).filter( reg_alias.user_id == user.id, reg_alias.registration_form_id == Registration.registration_form_id, ~reg_alias.is_deleted) registrations = (Registration.query.filter( Registration.user_id.is_(None), Registration.email.in_(user.all_emails), ~subquery.exists(), ~Registration.is_deleted).order_by( Registration.submitted_dt.desc()).all()) if not registrations: return done = set() for registration in registrations: if registration.registration_form_id in done: continue logger.info('Associating %s with %s', registration, user) registration.user = user done.add(registration.registration_form_id) db.session.flush() num = len(done) flash( ngettext('A registration has been linked to your account.', '{n} registrations have been linked to your account.', num).format(n=num), 'info')
def get_attachment_count(category_id=None): """Get the number of attachments in events in a category. :param category_id: The category ID to get statistics for. Attachments from subcategories are also included. :return: The number of attachments """ category_filter = Event.category_chain_overlaps(category_id) if category_id else True subcontrib_contrib = db.aliased(Contribution) query = (db.session .query(db.func.count(Attachment.id)) .join(Attachment.folder) .join(AttachmentFolder.event) .outerjoin(AttachmentFolder.session) .outerjoin(AttachmentFolder.contribution) .outerjoin(AttachmentFolder.subcontribution) .outerjoin(subcontrib_contrib, subcontrib_contrib.id == SubContribution.contribution_id) .filter(AttachmentFolder.link_type != LinkType.category, ~Attachment.is_deleted, ~AttachmentFolder.is_deleted, ~Event.is_deleted, # we have exactly one of those or none if the attachment is on the event itself ~db.func.coalesce(Session.is_deleted, Contribution.is_deleted, SubContribution.is_deleted, False), # in case of a subcontribution we also need to check that the contrib is not deleted (subcontrib_contrib.is_deleted.is_(None) | ~subcontrib_contrib.is_deleted), category_filter)) return query.scalar()
def get_attachment_count(category_id=None): """Get the number of attachments in events in a category. :param category_id: The category ID to get statistics for. Attachments from subcategories are also included. :return: The number of attachments """ category_filter = Event.category_chain_overlaps( category_id) if category_id else True subcontrib_contrib = db.aliased(Contribution) query = ( db.session.query(db.func.count(Attachment.id)).join( Attachment.folder).join(AttachmentFolder.event).outerjoin( AttachmentFolder.session).outerjoin( AttachmentFolder.contribution).outerjoin( AttachmentFolder.subcontribution).outerjoin( subcontrib_contrib, subcontrib_contrib.id == SubContribution.contribution_id). filter( AttachmentFolder.link_type != LinkType.category, ~Attachment.is_deleted, ~AttachmentFolder.is_deleted, ~Event.is_deleted, # we have exactly one of those or none if the attachment is on the event itself ~db.func.coalesce(Session.is_deleted, Contribution.is_deleted, SubContribution.is_deleted, False), # in case of a subcontribution we also need to check that the contrib is not deleted (subcontrib_contrib.is_deleted.is_(None) | ~subcontrib_contrib.is_deleted), category_filter)) return query.scalar()
def get_protection_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id, cat_alias.protection_mode]) .where(cat_alias.parent_id.is_(None)) .cte(recursive=True)) rec_query = (select([cat_alias.id, db.case({ProtectionMode.inheriting.value: cte_query.c.protection_mode}, else_=cat_alias.protection_mode, value=cat_alias.protection_mode)]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def get_protection_parent_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id, db.cast(literal(None), db.Integer).label('protection_parent')]) .where(cat_alias.parent_id.is_(None)) .cte(recursive=True)) rec_query = (select([cat_alias.id, db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, 0)}, else_=cat_alias.id, value=cat_alias.protection_mode)]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def get_merged_from_users_recursive(self): """Get the users merged into this users recursively.""" user_alias = db.aliased(User) cte_query = (select([ user_alias.id.label('merged_from_id') ]).where(user_alias.merged_into_id == self.id).cte(recursive=True)) rec_query = (select([ user_alias.id ]).where(user_alias.merged_into_id == cte_query.c.merged_from_id)) cte = cte_query.union_all(rec_query) return User.query.join(cte, User.id == cte.c.merged_from_id).all()
def get_icon_data_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata]) .where(cat_alias.parent_id.is_(None)) .cte(recursive=True)) rec_query = (select([cat_alias.id, db.case({'null': cte_query.c.source_id}, else_=cat_alias.id, value=db.func.json_typeof(cat_alias.icon_metadata)), db.case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata, value=db.func.json_typeof(cat_alias.icon_metadata))]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def _query_events(categ_ids, day_start, day_end): event = db.aliased(Event) dates_overlap = lambda t: (t.start_dt >= day_start) & (t.start_dt <= day_end) return (db.session.query(Event.id, TimetableEntry.start_dt).filter( Event.category_chain_overlaps(categ_ids), ~Event.is_deleted, ((Event.timetable_entries.any(dates_overlap(TimetableEntry))) | (Event.query.exists().where( Event.happens_between(day_start, day_end) & (Event.id == event.id))))).group_by( Event.id, TimetableEntry.start_dt).order_by( Event.id, TimetableEntry.start_dt).join( TimetableEntry, (TimetableEntry.event_id == Event.id) & (dates_overlap(TimetableEntry)), isouter=True))
def get_subtree_ids_cte(cls, ids): """Create a CTE for a category subtree. This CTE contains a single ``id`` column that contains all the specified IDs and those of all their subcategories. This is likely to be much more performant than `get_tree_cte` when the query is used with LIMIT, especially in large databases. """ cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id]) .where(cat_alias.id.in_(ids)) .cte(recursive=True)) rec_query = (select([cat_alias.id]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def _query_events(categ_ids, day_start, day_end): event = db.aliased(Event) dates_overlap = lambda t: (t.start_dt >= day_start) & (t.start_dt <= day_end) return (db.session.query(Event.id, TimetableEntry.start_dt) .filter( Event.category_chain_overlaps(categ_ids), ~Event.is_deleted, ((Event.timetable_entries.any(dates_overlap(TimetableEntry))) | (Event.query.exists().where( Event.happens_between(day_start, day_end) & (Event.id == event.id))))) .group_by(Event.id, TimetableEntry.start_dt) .order_by(Event.id, TimetableEntry.start_dt) .join(TimetableEntry, (TimetableEntry.event_id == Event.id) & (dates_overlap(TimetableEntry)), isouter=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 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 _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).scalar_subquery() 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).scalar_subquery()) 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).scalar_subquery()) 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.has_children -- whether the category contains any # non-deleted subcategories cat_alias = db.aliased(Category) query = (exists( [1]).where((cat_alias.parent_id == Category.id) & ~cat_alias.is_deleted).correlate_except(cat_alias)) Category.has_children = 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).scalar_subquery() Category.chain_titles = column_property(query, deferred=True) # Category.chain_ids -- a list of the ids in the parent chain, # starting with the root category down to the current category. # This is equivalent to the `category_chain` in the Event model. cte = Category.get_tree_cte() query = select([ cte.c.path ]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery() Category.chain_ids = 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).scalar_subquery() 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).scalar_subquery() 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).scalar_subquery() Category.deep_children_count = column_property(query, deferred=True)
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 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)