def filterSearchQuery(searchValuesDict, query, table):
"""This function takes an SQLAlchemy ORM query object and applies filters to
it using the keys and values from the searchValuesDict. The newly filtered
query is returned.
"""
searchTerm1 = escapeUnderscores(
removeWhiteSpace(searchValuesDict['searchTerm1']))
searchType1 = searchValuesDict['searchType1']
searchLocation1 = searchValuesDict['searchLocation1']
searchTerm2 = escapeUnderscores(
removeWhiteSpace(searchValuesDict['searchTerm2']))
searchType2 = searchValuesDict['searchType2']
searchLocation2 = searchValuesDict['searchLocation2']
# Translate the search terms into the storage orthography, if necessary
searchTerm1 = orthoTranslateSearchTerm(searchTerm1, searchLocation1)
searchTerm2 = orthoTranslateSearchTerm(searchTerm2, searchLocation2)
andOrNot = searchValuesDict['andOrNot']
restrictors = searchValuesDict['restrictors']
dateRestrictors = searchValuesDict['dateRestrictors']
if 'integerRestrictors' in searchValuesDict:
integerRestrictors = searchValuesDict['integerRestrictors']
else:
integerRestrictors = []
if 'emptyRestrictors' in searchValuesDict:
emptyRestrictors = searchValuesDict['emptyRestrictors']
else:
emptyRestrictors = []
orderByColumn = searchValuesDict['orderByColumn']
orderByDirection = searchValuesDict['orderByDirection']
# Modify the query object by adding a left outer join to the keyword and
# formkeyword tables if appropriate.
kwRestrictors = [r for r in restrictors if r['location'] == 'keywords']
if sum([len(r['options']) for r in kwRestrictors]):
query = query.outerjoin(model.formkeyword_table, model.Keyword)
# Modify the query object by adding a left outer join to the Gloss table
# if appropriate.
ggRestrictors = [r for r in restrictors
if r['location'] == 'glossGrammaticality']
if sum([len(r['options']) for r in ggRestrictors]) or \
(searchTerm1 and searchLocation1 == 'gloss') or \
(searchTerm2 and searchLocation2 == 'gloss'):
query = query.outerjoin(model.Form.glosses)
# Get the filter condition of the first search statement
filterCondition1 = getFilterCondition(searchTerm1, searchType1,
searchLocation1, table)
# Get the filter condition by coordinating the filter conditions of the two
# search statements, if there is a second such statement
if searchTerm2:
filterCondition2 = getFilterCondition(searchTerm2, searchType2,
searchLocation2, table)
if andOrNot == 'and_':
filterCondition = and_(filterCondition1, filterCondition2)
elif andOrNot == 'or_':
filterCondition = or_(filterCondition1, filterCondition2)
else:
filterCondition = and_(filterCondition1, not_(filterCondition2))
else:
filterCondition = filterCondition1
query = query.filter(filterCondition)
# General restrictors
for restrictor in restrictors:
if restrictor['options']:
query = filterQueryByRestrictor(query, restrictor, table)
# Date restrictors
for dateRestrictor in dateRestrictors:
if dateRestrictor['date']:
query = filterQueryByDateRestrictor(query, dateRestrictor, table)
# Integer restrictors
for integerRestrictor in integerRestrictors:
if integerRestrictor['integer']:
query = filterQueryByIntegerRestrictor(query, integerRestrictor, table)
# Empty restrictors
for emptyRestrictor in emptyRestrictors:
if emptyRestrictor['relation']:
query = filterQueryByEmptyRestrictor(query, emptyRestrictor, table)
# Order by
query = orderQuery(query, orderByColumn, orderByDirection, table)
return query
def filterSearchQuery(searchValuesDict, query, table):
"""Function takes a SQLAlchemy ORM query and filters it
using the keys and values from the HTML search form."""
searchTerm1 = escapeUnderscores(
removeWhiteSpace(searchValuesDict['searchTerm1']))
searchType1 = searchValuesDict['searchType1']
searchLocation1 = searchValuesDict['searchLocation1']
searchTerm2 = escapeUnderscores(
removeWhiteSpace(searchValuesDict['searchTerm2']))
searchType2 = searchValuesDict['searchType2']
searchLocation2 = searchValuesDict['searchLocation2']
# Translate the search terms into the storage orthography, if necessary
searchTerm1 = orthoTranslateSearchTerm(searchTerm1, searchLocation1)
searchTerm2 = orthoTranslateSearchTerm(searchTerm2, searchLocation2)
andOrNot = searchValuesDict['andOrNot']
restrictors = searchValuesDict['restrictors']
dateRestrictors = searchValuesDict['dateRestrictors']
if 'integerRestrictors' in searchValuesDict:
integerRestrictors = searchValuesDict['integerRestrictors']
else:
integerRestrictors = []
orderByColumn = searchValuesDict['orderByColumn']
orderByDirection = searchValuesDict['orderByDirection']
# Dictionary from search type value to appropriate filter function
functionsBySearchType = {
'as a phrase': filterBySingleTerm,
'as a reg exp': filterBySingleTerm,
'exactly': filterBySingleTerm,
'all of these': filterByMultipleTerms,
'any of these': filterByMultipleTerms
}
# Get strings representing the filter(s) on the query:
filterString = functionsBySearchType[searchType1](searchTerm1, searchType1,
searchLocation1, table)
if searchTerm2:
filterString2 = functionsBySearchType[searchType2](searchTerm2,
searchType2,
searchLocation2,
table)
# Put boolean operators in the right place, with brackets
if andOrNot == 'not_':
filterString = 'and_(%s, not_(%s))' % (filterString, filterString2)
else:
filterString = '%s(%s, %s)' % (andOrNot, filterString,
filterString2)
# Add a a left outer join to the keyword and formkeyword tables if appropriate
# otherwise add a left outer join to the gloss table if appropriate,
# otherwise no left outer join.
keywordsRestrictors = [
restrictor for restrictor in restrictors
if restrictor['location'] == 'keywords'
]
if sum([len(restrictor['options']) for restrictor in keywordsRestrictors]):
outerjoin = '.outerjoin(model.Form.glosses, model.formkeyword_table, model.Keyword)'
elif (searchTerm1
and searchLocation1 == 'gloss') or (searchTerm2
and searchLocation2 == 'gloss'):
outerjoin = '.outerjoin(model.Form.glosses)'
else:
outerjoin = ''
# Compose the right hand side of the filtered query expression
filterString = 'query%s.filter(%s)' % (outerjoin, filterString)
# Evaluate the object and methods mentioned in the string using Python's eval(),
# Update the query object using Python's exec()
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are restrictors, filter the query further
for restrictor in restrictors:
if len(restrictor['options']):
filterString = filterByRestrictor(restrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are date restrictors, filter the query further
for dateRestrictor in dateRestrictors:
if dateRestrictor['date']:
filterString = filterByDateRestrictor(dateRestrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are integer restrictors, filter the query further
for integerRestrictor in integerRestrictors:
if integerRestrictor['integer']:
filterString = filterByIntegerRestrictor(integerRestrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# Finally add the ordering
query = eval('query.order_by(%s(model.%s.%s))' %
(orderByDirection, table, orderByColumn))
#query = query.order_by(desc(model.Form.transcription))
# Return the filtered query
return query
def filterSearchQuery(searchValuesDict, query, table):
"""Function takes a SQLAlchemy ORM query and filters it
using the keys and values from the HTML search form."""
searchTerm1 = escapeUnderscores(removeWhiteSpace(searchValuesDict['searchTerm1']))
searchType1 = searchValuesDict['searchType1']
searchLocation1 = searchValuesDict['searchLocation1']
searchTerm2 = escapeUnderscores(removeWhiteSpace(searchValuesDict['searchTerm2']))
searchType2 = searchValuesDict['searchType2']
searchLocation2 = searchValuesDict['searchLocation2']
# Translate the search terms into the storage orthography, if necessary
searchTerm1 = orthoTranslateSearchTerm(searchTerm1, searchLocation1)
searchTerm2 = orthoTranslateSearchTerm(searchTerm2, searchLocation2)
andOrNot = searchValuesDict['andOrNot']
restrictors = searchValuesDict['restrictors']
dateRestrictors = searchValuesDict['dateRestrictors']
if 'integerRestrictors' in searchValuesDict:
integerRestrictors = searchValuesDict['integerRestrictors']
else:
integerRestrictors = []
orderByColumn = searchValuesDict['orderByColumn']
orderByDirection = searchValuesDict['orderByDirection']
# Dictionary from search type value to appropriate filter function
functionsBySearchType = {
'as a phrase': filterBySingleTerm,
'as a reg exp': filterBySingleTerm,
'exactly': filterBySingleTerm,
'all of these': filterByMultipleTerms,
'any of these': filterByMultipleTerms
}
# Get strings representing the filter(s) on the query:
filterString = functionsBySearchType[searchType1](searchTerm1, searchType1, searchLocation1, table)
if searchTerm2:
filterString2 = functionsBySearchType[searchType2](searchTerm2, searchType2, searchLocation2, table)
# Put boolean operators in the right place, with brackets
if andOrNot == 'not_':
filterString = 'and_(%s, not_(%s))' % (filterString, filterString2)
else:
filterString = '%s(%s, %s)' % (andOrNot, filterString, filterString2)
# Add a a left outer join to the keyword and formkeyword tables if appropriate
# otherwise add a left outer join to the gloss table if appropriate,
# otherwise no left outer join.
keywordsRestrictors = [restrictor for restrictor in restrictors if restrictor['location'] == 'keywords']
if sum([len(restrictor['options']) for restrictor in keywordsRestrictors]):
outerjoin = '.outerjoin(model.Form.glosses, model.formkeyword_table, model.Keyword)'
elif (searchTerm1 and searchLocation1 == 'gloss') or (searchTerm2 and searchLocation2 == 'gloss'):
outerjoin = '.outerjoin(model.Form.glosses)'
else:
outerjoin = ''
# Compose the right hand side of the filtered query expression
filterString = 'query%s.filter(%s)' % (outerjoin, filterString)
# Evaluate the object and methods mentioned in the string using Python's eval(),
# Update the query object using Python's exec()
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are restrictors, filter the query further
for restrictor in restrictors:
if len(restrictor['options']):
filterString = filterByRestrictor(restrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are date restrictors, filter the query further
for dateRestrictor in dateRestrictors:
if dateRestrictor['date']:
filterString = filterByDateRestrictor(dateRestrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# If there are integer restrictors, filter the query further
for integerRestrictor in integerRestrictors:
if integerRestrictor['integer']:
filterString = filterByIntegerRestrictor(integerRestrictor, table)
filterString = 'query.filter(%s)' % filterString
cmd = "query = eval('%s' % filterString)"
exec(cmd)
# Finally add the ordering
query = eval('query.order_by(%s(model.%s.%s))' % (orderByDirection, table, orderByColumn))
#query = query.order_by(desc(model.Form.transcription))
# Return the filtered query
return query
