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