def build_user_search_query(criteria, exact=False, include_deleted=False, include_pending=False): unspecified = object() query = User.query.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") name = name.split() query = query.filter(_build_name_search(name)) for k, v in criteria.iteritems(): query = query.filter(unaccent_match(getattr(User, k), v, exact)) return query
def build_user_search_query(criteria, exact=False, include_deleted=False, include_pending=False, include_blocked=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) if not include_blocked: query = query.filter(~User.is_blocked) 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.items(): 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 _getAnswer(self): event_persons = [] criteria = { 'surName': self._surName, 'name': self._name, 'organisation': self._organisation, 'email': self._email } users = search_avatars(criteria, self._exactMatch, self._searchExt) if self._event: fields = { EventPerson.first_name: self._name, EventPerson.last_name: self._surName, EventPerson.email: self._email, EventPerson.affiliation: self._organisation } criteria = [ unaccent_match(col, val, exact=self._exactMatch) for col, val in fields.iteritems() ] event_persons = self._event.persons.filter(*criteria).all() fossilized_users = fossilize( sorted(users, key=lambda av: (av.getStraightFullName(), av.getEmail()))) fossilized_event_persons = map(serialize_event_person, event_persons) unique_users = { to_unicode(user['email']): user for user in chain(fossilized_users, fossilized_event_persons) } return sorted(unique_users.values(), key=lambda x: (to_unicode(x['name']).lower(), to_unicode(x['email'])))
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 _getAnswer(self): event_persons = [] users = searchUsers(self._surName, self._name, self._organisation, self._email, self._exactMatch, self._searchExt) if self._event: fields = {EventPerson.first_name: self._name, EventPerson.last_name: self._surName, EventPerson.email: self._email, EventPerson.affiliation: self._organisation} criteria = [unaccent_match(col, val, exact=self._exactMatch) for col, val in fields.iteritems()] event_persons = self._event.persons.filter(*criteria).all() fossilized_users = fossilize(sorted(users, key=lambda av: (av.getStraightFullName(), av.getEmail()))) fossilized_event_persons = map(serialize_event_person, event_persons) unique_users = {to_unicode(user['email']): user for user in chain(fossilized_users, fossilized_event_persons)} return sorted(unique_users.values(), key=lambda x: (to_unicode(x['name']).lower(), to_unicode(x['email'])))
def search_users(exact=False, include_deleted=False, include_pending=False, external=False, **criteria): """Searches for users. :param exact: Indicates if only exact matches should be returned. This is MUCH faster than a non-exact saerch, especially when searching external users. :param include_deleted: Indicates if also users marked as deleted should be returned. :param include_pending: Indicates if also users who are still pending should be returned. :param external: Indicates if identity providers should be searched for matching users. :param criteria: A dict containing any of the following keys: first_name, last_name, email, affiliation, phone, address :return: A set of matching users. If `external` was set, it may contain both :class:`~flask_multipass.IdentityInfo` objects for external users not yet in Indico and :class:`.User` objects for existing users. """ unspecified = object() query = User.query.options(db.joinedload(User.identities), db.joinedload(User._all_emails), db.joinedload(User.merged_into_user)) criteria = { key: value.strip() for key, value in criteria.iteritems() if value.strip() } original_criteria = dict(criteria) if not criteria: return set() if not include_pending: query = query.filter(~User.is_pending) if not include_deleted: query = query.filter(~User.is_deleted) organisation = criteria.pop('affiliation', unspecified) if organisation is not unspecified: query = query.join(UserAffiliation).filter( unaccent_match(UserAffiliation.name, organisation, exact)) email = criteria.pop('email', unspecified) if email is not unspecified: query = query.join(UserEmail).filter( unaccent_match(UserEmail.email, email, exact)) for k, v in criteria.iteritems(): query = query.filter(unaccent_match(getattr(User, k), v, exact)) found_emails = {} found_identities = {} for user in query: for identity in user.identities: found_identities[(identity.provider, identity.identifier)] = user for email in user.all_emails: found_emails[email] = user # external user providers if external: identities = multipass.search_identities(exact=exact, **original_criteria) for ident in identities: if not ident.data.get('email'): # Skip users with no email continue if ((ident.provider.name, ident.identifier) not in found_identities and ident.data['email'].lower() not in found_emails): found_emails[ident.data['email'].lower()] = ident found_identities[(ident.provider, ident.identifier)] = ident return set(found_emails.viewvalues())
EventPerson.contribution_links.any( ContributionPersonLink.contribution.has( ~Contribution.is_deleted)), EventPerson.event_links.any(), EventPerson.subcontribution_links.any( SubContributionPersonLink.subcontribution.has( and_( ~SubContribution.is_deleted, SubContribution.contribution.has( ~Contribution.is_deleted)))), EventPerson.session_block_links.any(), )) for k, v in criteria.items(): query = query.filter( unaccent_match(getattr(EventPerson, k), v, exact)) # wrap as subquery so we can apply order regardless of distinct-by-id query = query.from_self() query = query.order_by( db.func.lower( db.func.indico.indico_unaccent(EventPerson.first_name)), db.func.lower(db.func.indico.indico_unaccent( EventPerson.last_name)), EventPerson.id, ) return query.limit(10).all(), query.count() @use_kwargs( { 'first_name': fields.Str(validate=validate.Length(min=1)),
def search_users(exact=False, include_deleted=False, include_pending=False, external=False, **criteria): """Searches for users. :param exact: Indicates if only exact matches should be returned. This is MUCH faster than a non-exact saerch, especially when searching external users. :param include_deleted: Indicates if also users marked as deleted should be returned. :param include_pending: Indicates if also users who are still pending should be returned. :param external: Indicates if identity providers should be searched for matching users. :param criteria: A dict containing any of the following keys: first_name, last_name, email, affiliation, phone, address :return: A set of matching users. If `external` was set, it may contain both :class:`~flask_multipass.IdentityInfo` objects for external users not yet in Indico and :class:`.User` objects for existing users. """ unspecified = object() query = User.query.options(db.joinedload(User.identities), db.joinedload(User._all_emails), db.joinedload(User.merged_into_user)) criteria = {key: value.strip() for key, value in criteria.iteritems() if value.strip()} original_criteria = dict(criteria) if not criteria: return set() if not include_pending: query = query.filter(~User.is_pending) if not include_deleted: query = query.filter(~User.is_deleted) organisation = criteria.pop('affiliation', unspecified) if organisation is not unspecified: query = query.join(UserAffiliation).filter(unaccent_match(UserAffiliation.name, organisation, exact)) email = criteria.pop('email', unspecified) if email is not unspecified: query = query.join(UserEmail).filter(unaccent_match(UserEmail.email, email, exact)) for k, v in criteria.iteritems(): query = query.filter(unaccent_match(getattr(User, k), v, exact)) found_emails = {} found_identities = {} for user in query: for identity in user.identities: found_identities[(identity.provider, identity.identifier)] = user for email in user.all_emails: found_emails[email] = user # external user providers if external: identities = multipass.search_identities(exact=exact, **original_criteria) for ident in identities: if not ident.data.get('email'): # Skip users with no email continue if ((ident.provider.name, ident.identifier) not in found_identities and ident.data['email'].lower() not in found_emails): found_emails[ident.data['email'].lower()] = ident found_identities[(ident.provider, ident.identifier)] = ident return set(found_emails.viewvalues())
def _getAnswer(self): event_persons = [] event_abstract_submitters = [] criteria = { 'surName': self._surName, 'name': self._name, 'organisation': self._organisation, 'email': self._email, 'abstract': self._abstract, 'track': self._track } users = search_avatars(criteria, self._exactMatch, self._searchExt) users2 = users if self._event: fields = { EventPerson.first_name: self._name, EventPerson.last_name: self._surName, EventPerson.email: self._email, EventPerson.affiliation: self._organisation } criteria = [ unaccent_match(col, val, exact=self._exactMatch) for col, val in fields.iteritems() ] if not self._abstract: if not self._track: event_persons = self._event.persons.filter(*criteria).all() event_abstract_submitters = event_persons else: event_abstract_submitters = EventPerson.query.join( Abstract, (Abstract.submitter_id == EventPerson.user_id) & (Abstract.event_id == self._event.id)).join( Track, (Abstract.accepted_track_id == Track.id)).with_entities( EventPerson.id, Abstract.title.label('abstract'), Track.title.label('track'), EventPerson.email, EventPerson.last_name.label('full_name'), EventPerson.first_name, EventPerson.last_name, EventPerson.title.label('title'), EventPerson.affiliation, EventPerson.phone, EventPerson.address, EventPerson.user_id).filter( Track.title.op("~*")( r'[[:<:]]{}[[:>:]]'.format( self._track))).filter( *criteria).all() elif not self._track: event_abstract_submitters = EventPerson.query.join( Abstract, (Abstract.submitter_id == EventPerson.user_id) & (Abstract.event_id == self._event.id)).with_entities( EventPerson.id, Abstract.title.label('abstract'), EventPerson.email, EventPerson.last_name.label('full_name'), EventPerson.first_name, EventPerson.last_name, EventPerson.title.label('title'), EventPerson.affiliation, EventPerson.phone, EventPerson.address, EventPerson.user_id).filter( unaccent_match( Abstract.title, self._abstract, self._exactMatch)).filter(*criteria).all() else: event_abstract_submitters = EventPerson.query.join( Abstract, (Abstract.submitter_id == EventPerson.user_id) & (Abstract.event_id == self._event.id)).join( Track, (Abstract.accepted_track_id == Track.id)).with_entities( EventPerson.id, Abstract.title.label('abstract'), Track.title.label('track'), EventPerson.email, EventPerson.last_name.label('full_name'), EventPerson.first_name, EventPerson.last_name, EventPerson.title.label('title'), EventPerson.affiliation, EventPerson.phone, EventPerson.address, EventPerson.user_id).filter( unaccent_match( Abstract.title, self._abstract, self._exactMatch)).filter( Track.title.op("~*")( r'[[:<:]]{}[[:>:]]'.format( self._track))).filter( *criteria).all() fossilized_users = fossilize( sorted(users, key=lambda av: (av.getStraightFullName(), av.getEmail()))) fossilized_abstract_submitters = map(serialize_event_person, event_abstract_submitters) for submitter in fossilized_abstract_submitters: for event_submitter in event_abstract_submitters: if (self._abstract or self._track) and submitter['id'] == event_submitter[0]: submitter['abstract'] = event_submitter[1] if self._track: submitter['track'] = event_submitter[2] for usre in fossilized_users: for usre2 in users2: if hasattr(usre2.user, 'abstract' ): #and int(usre['id']) == int(usre2.user.id): usre['abstract'] = usre2.user.abstract usre['track'] = usre2.user.track if self._eventPerson: unique_users = { to_unicode(user['email']): user for user in fossilized_abstract_submitters } else: unique_users = { to_unicode(user['email']): user for user in chain(fossilized_users, fossilized_abstract_submitters) } return sorted(unique_users.values(), key=lambda x: (to_unicode(x['name']).lower(), to_unicode(x['email'])))
def search_users(exact=False, include_deleted=False, include_pending=False, external=False, allow_system_user=False, **criteria): """Searches for users. :param exact: Indicates if only exact matches should be returned. This is MUCH faster than a non-exact saerch, especially when searching external users. :param include_deleted: Indicates if also users marked as deleted should be returned. :param include_pending: Indicates if also users who are still pending should be returned. :param external: Indicates if identity providers should be searched for matching users. :param allow_system_user: Whether the system user may be returned in the search results. :param criteria: A dict containing any of the following keys: first_name, last_name, email, affiliation, phone, address :return: A set of matching users. If `external` was set, it may contain both :class:`~flask_multipass.IdentityInfo` objects for external users not yet in Indico and :class:`.User` objects for existing users. """ unspecified = object() query = User.query.options(db.joinedload(User.identities), db.joinedload(User._all_emails), db.joinedload(User.merged_into_user)) criteria = { key: value.strip() for key, value in criteria.iteritems() if value.strip() } original_criteria = dict(criteria) if not criteria: return set() 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)) abstract = criteria.pop('abstract', unspecified) track = criteria.pop('track', unspecified) query2 = query if abstract is not unspecified: if track is unspecified: query = query.join(Abstract, (Abstract.submitter_id == User.id)).filter( unaccent_match(Abstract.title, abstract, exact)) query2 = query2.join( Abstract, (Abstract.submitter_id == User.id)).filter( unaccent_match(Abstract.title, abstract, exact)).add_columns( User.id, Track.title.label('track'), Abstract.title.label('abstract')) else: query = query.join( Abstract, (Abstract.submitter_id == User.id)).join( Track, (Abstract.accepted_track_id == Track.id)).filter( unaccent_match( Abstract.title, abstract, exact)).filter( Track.title.op("~*")( r'[[:<:]]{}[[:>:]]'.format(track))) query2 = query2.join( Abstract, (Abstract.submitter_id == User.id)).add_columns( User.id, Track.title.label('track'), Abstract.title.label('abstract')).join( Track, (Abstract.accepted_track_id == Track.id)).filter( unaccent_match( Abstract.title, abstract, exact)).filter( Track.title.op("~*")( r'[[:<:]]{}[[:>:]]'.format(track))) elif track is not unspecified: query = query.join(Abstract, (Abstract.submitter_id == User.id)).join( Track, (Abstract.accepted_track_id == Track.id)).filter( Track.title.op("~*")(r'[[:<:]]{}[[:>:]]'.format(track))) query2 = query2.join( Abstract, (Abstract.submitter_id == User.id)).add_columns( User.id, Track.title.label('track'), Abstract.title.label('abstract')).join( Track, (Abstract.accepted_track_id == Track.id)).filter( Track.title.op("~*")( r'[[:<:]]{}[[:>:]]'.format(track))) for k, v in criteria.iteritems(): query = query.filter(unaccent_match(getattr(User, k), v, exact)) for user1 in query: for user2 in query2: if (user1.id == user2.id) and ((abstract is not unspecified) or (track is not unspecified)): user1.abstract = user2.abstract user1.track = user2.track found_emails = {} found_identities = {} system_user = set() for user in query: for identity in user.identities: found_identities[(identity.provider, identity.identifier)] = user for email in user.all_emails: found_emails[email] = user if user.is_system and not user.all_emails and allow_system_user: system_user = {user} # external user providers if external: identities = multipass.search_identities(exact=exact, **original_criteria) for ident in identities: if not ident.data.get('email'): # Skip users with no email continue if ((ident.provider.name, ident.identifier) not in found_identities and ident.data['email'].lower() not in found_emails): found_emails[ident.data['email'].lower()] = ident found_identities[(ident.provider, ident.identifier)] = ident return set(found_emails.viewvalues()) | system_user
query = query.filter( or_( EventPerson.abstract_links.any(AbstractPersonLink.abstract.has(~Abstract.is_deleted)), EventPerson.contribution_links.any(ContributionPersonLink.contribution.has(~Contribution.is_deleted)), EventPerson.event_links.any(), EventPerson.subcontribution_links.any( SubContributionPersonLink.subcontribution.has( and_(~SubContribution.is_deleted, SubContribution.contribution.has(~Contribution.is_deleted)) ) ), EventPerson.session_block_links.any(), ) ) for k, v in criteria.items(): query = query.filter(unaccent_match(getattr(EventPerson, k), v, exact)) # wrap as subquery so we can apply order regardless of distinct-by-id query = query.from_self() query = query.order_by( db.func.lower(db.func.indico.indico_unaccent(EventPerson.first_name)), db.func.lower(db.func.indico.indico_unaccent(EventPerson.last_name)), EventPerson.id, ) return query.limit(10).all(), query.count() @use_kwargs({ 'first_name': fields.Str(validate=validate.Length(min=1)), 'last_name': fields.Str(validate=validate.Length(min=1)), 'email': fields.Str(validate=lambda s: len(s) > 3), 'affiliation': fields.Str(validate=validate.Length(min=1)),