def _build_name_search(name_list): text = remove_accents('%{}%'.format('%'.join( escape_like(name) for name in name_list))) return db.or_( db.func.indico.indico_unaccent( db.func.concat(User.first_name, ' ', User.last_name)).ilike(text), db.func.indico.indico_unaccent( db.func.concat(User.last_name, ' ', User.first_name)).ilike(text))
def title_matches(cls, search_string, exact=False): """Check whether the title matches a search string. To be used in a SQLAlchemy `filter` call. :param search_string: A string to search for :param exact: Whether to search for the exact string """ crit = db.func.to_tsvector('simple', cls.title).match(preprocess_ts_string(search_string), postgresql_regconfig='simple') if exact: crit = crit & cls.title.ilike('%{}%'.format(escape_like(search_string))) return crit
def title_matches(cls, search_string, exact=False): """Check whether the title matches a search string. To be used in a SQLAlchemy `filter` call. :param search_string: A string to search for :param exact: Whether to search for the exact string """ crit = db.func.to_tsvector('simple', cls.title).match( preprocess_ts_string(search_string), postgresql_regconfig='simple') if exact: crit = crit & cls.title.ilike('%{}%'.format( escape_like(search_string))) return crit
def fts_matches(column, search_string, *, exact=False): """Check whether a fts-indexed column matches a search string. To be used in a SQLAlchemy `filter` call. :param column: The column to search in :param search_string: A string to search for :param exact: Whether to search for the exact string """ crit = db.func.to_tsvector('simple', column).match( preprocess_ts_string(search_string), postgresql_regconfig='simple') if exact: crit = crit & column.ilike(escape_like(search_string)) return crit
def find_with_filters(filters, user=None): from indico.modules.rb.models.locations import Location equipment_count = len(filters.get('available_equipment', ())) equipment_subquery = None if equipment_count: equipment_subquery = ( db.session.query(RoomEquipmentAssociation) .with_entities(func.count(RoomEquipmentAssociation.c.room_id)) .filter( RoomEquipmentAssociation.c.room_id == Room.id, RoomEquipmentAssociation.c.equipment_id.in_(eq.id for eq in filters['available_equipment']) ) .correlate(Room) .as_scalar() ) capacity = filters.get('capacity') q = ( Room.query .join(Location.rooms) .filter( Location.id == filters['location'].id if filters.get('location') else True, ((Room.capacity >= (capacity * 0.8)) | (Room.capacity == None)) if capacity else True, Room.is_reservable if filters.get('is_only_public') else True, Room.is_auto_confirm if filters.get('is_auto_confirm') else True, Room.is_active if filters.get('is_only_active', False) else True, (equipment_subquery == equipment_count) if equipment_subquery is not None else True) ) if filters.get('available', -1) != -1: repetition = RepeatMapping.convert_legacy_repeatability(ast.literal_eval(filters['repeatability'])) is_available = Room.filter_available(filters['start_dt'], filters['end_dt'], repetition, include_pre_bookings=filters.get('include_pre_bookings', True), include_pending_blockings=filters.get('include_pending_blockings', True)) # Filter the search results if filters['available'] == 0: # booked/unavailable q = q.filter(~is_available) elif filters['available'] == 1: # available q = q.filter(is_available) else: raise ValueError('Unexpected availability value') free_search_columns = ( 'name', 'site', 'division', 'building', 'floor', 'number', 'telephone', 'key_location', 'comments' ) if filters.get('details'): # Attributes are stored JSON-encoded, so we need to JSON-encode the provided string and remove the quotes # afterwards since PostgreSQL currently does not expose a function to decode a JSON string: # http://www.postgresql.org/message-id/[email protected] details = filters['details'].lower() details_str = u'%{}%'.format(escape_like(details)) details_json = u'%{}%'.format(escape_like(json.dumps(details)[1:-1])) free_search_criteria = [getattr(Room, c).ilike(details_str) for c in free_search_columns] free_search_criteria.append(Room.attributes.any(cast(RoomAttributeAssociation.value, db.String) .ilike(details_json))) q = q.filter(or_(*free_search_criteria)) q = q.order_by(Room.capacity) rooms = q.all() # Apply a bunch of filters which are *much* easier to do here than in SQL! if filters.get('is_only_public'): # This may trigger additional SQL queries but is_public is cached and doing this check here is *much* easier rooms = [r for r in rooms if r.is_public] if filters.get('is_only_my_rooms'): assert user is not None rooms = [r for r in rooms if r.is_owned_by(user)] if capacity: # Unless it would result in an empty resultset we don't want to show rooms with >20% more capacity # than requested. This cannot be done easily in SQL so we do that logic here after the SQL query already # weeded out rooms that are too small matching_capacity_rooms = [r for r in rooms if r.capacity is None or r.capacity <= capacity * 1.2] if matching_capacity_rooms: rooms = matching_capacity_rooms return rooms
def _make_room_text_filter(text): text = '%{}%'.format(escape_like(text)) columns = ('site', 'division', 'building', 'floor', 'number', 'comments', 'full_name') return db.or_(getattr(Room, col).ilike(text) for col in columns)
def _build_name_search(name_list): text = remove_accents('%{}%'.format('%'.join(escape_like(name) for name in name_list))) return db.or_(db.func.indico.indico_unaccent(db.func.concat(User.first_name, ' ', User.last_name)).ilike(text), db.func.indico.indico_unaccent(db.func.concat(User.last_name, ' ', User.first_name)).ilike(text))