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 _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))
