def _build_search_query(self, search_query):
"""
Search GXDIndexRecord by fields:
_refs_key
_marker_key
_priority_key
_conditionalmutants_key
comments
_modifiedby_key
_modifiedby_key
"""
query = GxdIndexRecord.query
# join to Marker for sorting
query = query.join(GxdIndexRecord.marker)
# join to Reference for sorting
# also for query
query = query.join(GxdIndexRecord.reference)
hasRefsKeySearch = search_query.has_valid_param('_refs_key')
if hasRefsKeySearch:
_refs_key = search_query.get_value('_refs_key')
query = query.filter(GxdIndexRecord._refs_key==_refs_key)
if not hasRefsKeySearch:
# reference-related searches only need to occur if we did not
# also search a specific _Refs_key
if search_query.has_valid_param('short_citation'):
short_citation = search_query.get_value('short_citation')
short_citation = short_citation.lower()
query = query.join(Reference.citation_cache)
query = query.filter(
db.func.lower(ReferenceCitationCache.short_citation).like(short_citation)
)
hasMarkerKeySearch = search_query.has_valid_param('_marker_key')
if hasMarkerKeySearch:
_marker_key = search_query.get_value('_marker_key')
query = query.filter(GxdIndexRecord._marker_key==_marker_key)
if not hasMarkerKeySearch:
# marker-related searches only needs to occur if we did not
# also search a specific _marker_key
if search_query.has_valid_param('marker_symbol'):
marker_symbol = search_query.get_value('marker_symbol')
marker_symbol = marker_symbol.lower()
query = query.filter(
db.func.lower(Marker.symbol).like(marker_symbol)
)
if search_query.has_valid_param('_priority_key'):
_priority_key = search_query.get_value('_priority_key')
query = query.filter(GxdIndexRecord._priority_key==_priority_key)
if search_query.has_valid_param('comments'):
comments = search_query.get_value('comments')
comments = comments.lower()
query = query.filter(db.func.lower(GxdIndexRecord.comments).like(comments))
if search_query.has_valid_param('is_coded'):
is_coded = search_query.get_value('is_coded')
query = query.filter(GxdIndexRecord.fully_coded==is_coded)
if search_query.has_valid_param('_conditionalmutants_key'):
_conditionalmutants_key = search_query.get_value('_conditionalmutants_key')
query = query.filter(GxdIndexRecord._conditionalmutants_key==_conditionalmutants_key)
if search_query.has_valid_param('_createdby_key'):
_createdby_key = search_query.get_value('_createdby_key')
query = query.filter(GxdIndexRecord._createdby_key==_createdby_key)
if search_query.has_valid_param('createdby_login'):
login = search_query.get_value('createdby_login')
login = login.lower()
createdby_alias = db.aliased(MGIUser)
query = query.join(createdby_alias, GxdIndexRecord.createdby)
query = query.filter(db.func.lower(createdby_alias.login).like(login))
if search_query.has_valid_param('_modifiedby_key'):
_modifiedby_key = search_query.get_value('_modifiedby_key')
query = query.filter(GxdIndexRecord._modifiedby_key==_modifiedby_key)
if search_query.has_valid_param('modifiedby_login'):
login = search_query.get_value('modifiedby_login')
login = login.lower()
modifiedby_alias = db.aliased(MGIUser)
query = query.join(modifiedby_alias, GxdIndexRecord.modifiedby)
query = query.filter(db.func.lower(modifiedby_alias.login).like(login))
if search_query.has_valid_param('creation_date'):
creation_date = search_query.get_value('creation_date')
query = DateHelper().build_query_with_date(query, GxdIndexRecord.creation_date, creation_date)
if search_query.has_valid_param('modification_date'):
modification_date = search_query.get_value('modification_date')
query = DateHelper().build_query_with_date(query, GxdIndexRecord.modification_date, modification_date)
if search_query.has_valid_param('indexstages'):
indexstages = search_query.get_value('indexstages')
# AND the stages together
logger.debug("Querying indexstages = %s" % indexstages)
for indexstage in indexstages:
sub_indexrecord = db.aliased(GxdIndexRecord)
sub_indexstage = db.aliased(GxdIndexStage)
sq = db.session.query(sub_indexrecord)
sq = sq.join(sub_indexstage, sub_indexrecord.indexstages)
sq = sq.filter(sub_indexstage._stageid_key==indexstage['_stageid_key'])
sq = sq.filter(sub_indexstage._indexassay_key==indexstage['_indexassay_key'])
sq = sq.filter(sub_indexrecord._index_key==GxdIndexRecord._index_key)
sq.correlate(GxdIndexRecord)
query = query.filter(sq.exists())
# OR the stages together
# sqs = []
# for indexstage in indexstages:
# sub_indexrecord = db.aliased(GxdIndexRecord)
# sub_indexstage = db.aliased(GxdIndexStage)
# sq = db.session.query(sub_indexrecord)
# sq = sq.join(sub_indexstage, sub_indexrecord.indexstages)
# sq = sq.filter(sub_indexstage._stageid_key==indexstage['_stageid_key'])
# sq = sq.filter(sub_indexstage._indexassay_key==indexstage['_indexassay_key'])
# sq = sq.filter(sub_indexrecord._index_key==GxdIndexRecord._index_key)
# sq.correlate(GxdIndexRecord)
#
# sqs.append(sq.exists())
#
# query = query.filter(db.or_(*sqs))
# eager-load the marker and reference relationships
query = query.options(db.subqueryload('marker'))
query = query.options(db.subqueryload('reference'))
query = query.order_by(Marker.symbol, Reference.authors)
return query
def _build_search_query(self, search_query):
query = GxdHTExperiment.query
if search_query.has_valid_param("name"):
name = search_query.get_value("name").lower()
query = query.filter(db.func.lower(GxdHTExperiment.name).like(name))
if search_query.has_valid_param("description"):
description = search_query.get_value("description").lower()
query = query.filter(db.func.lower(GxdHTExperiment.description).like(description))
if search_query.has_valid_param("evaluated_date"):
evaluated_date = search_query.get_value("evaluated_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.evaluated_date, evaluated_date)
if search_query.has_valid_param("initial_curated_date"):
initial_curated_date = search_query.get_value("initial_curated_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.initial_curated_date, initial_curated_date)
if search_query.has_valid_param("last_curated_date"):
last_curated_date = search_query.get_value("last_curated_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.last_curated_date, last_curated_date)
if search_query.has_valid_param("release_date"):
release_date = search_query.get_value("release_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.release_date, release_date)
if search_query.has_valid_param("lastupdate_date"):
lastupdate_date = search_query.get_value("lastupdate_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.lastupdate_date, lastupdate_date)
if search_query.has_valid_param("modification_date"):
modification_date = search_query.get_value("modification_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.modification_date, modification_date)
if search_query.has_valid_param("creation_date"):
creation_date = search_query.get_value("creation_date")
query = DateHelper().build_query_with_date(query, GxdHTExperiment.creation_date, creation_date)
if search_query.has_valid_param("_evaluationstate_key"):
evaluationstate_key = search_query.get_value("_evaluationstate_key")
query = query.filter(GxdHTExperiment._evaluationstate_key == int(evaluationstate_key))
if search_query.has_valid_param("_experiment_key"):
experiment_key = search_query.get_value("_experiment_key")
query = query.filter(GxdHTExperiment._experiment_key == int(experiment_key))
if search_query.has_valid_param("_curationstate_key"):
curationstate_key = search_query.get_value("_curationstate_key")
query = query.filter(GxdHTExperiment._curationstate_key == int(curationstate_key))
if search_query.has_valid_param("_studytype_key"):
studytype_key = search_query.get_value("_studytype_key")
query = query.filter(GxdHTExperiment._studytype_key == int(studytype_key))
if search_query.has_valid_param("_experimenttype_key"):
experimenttype_key = search_query.get_value("_experimenttype_key")
query = query.filter(GxdHTExperiment._experimenttype_key == int(experimenttype_key))
if search_query.has_valid_param("evaluatedby_object"):
user = db.aliased(MGIUser)
evaluatedby_object = search_query.get_value("evaluatedby_object")
login = evaluatedby_object["login"].lower()
if len(login) > 0:
query = query.join(user, GxdHTExperiment.evaluatedby_object).filter(db.func.lower(user.login).like(login))
if search_query.has_valid_param("initialcuratedby_object"):
user = db.aliased(MGIUser)
initialcuratedby_object = search_query.get_value("initialcuratedby_object")
login = initialcuratedby_object["login"].lower()
if len(login) > 0:
query = query.join(user, GxdHTExperiment.initialcuratedby_object).filter(db.func.lower(user.login).like(login))
if search_query.has_valid_param("lastcuratedby_object"):
user = db.aliased(MGIUser)
lastcuratedby_object = search_query.get_value("lastcuratedby_object")
login = lastcuratedby_object["login"].lower()
if len(login) > 0:
query = query.join(user, GxdHTExperiment.lastcuratedby_object).filter(db.func.lower(user.login).like(login))
if search_query.has_valid_param("primaryid"):
primaryid = search_query.get_value("primaryid").lower()
query = query.filter(db.func.lower(GxdHTExperiment.primaryid).like(primaryid))
if search_query.has_valid_param("secondaryid"):
accession = db.aliased(Accession)
secondaryid = search_query.get_value("secondaryid").lower()
query = query.join(accession, GxdHTExperiment.secondaryid_objects).filter(db.func.lower(accession.accid).like(secondaryid))
if search_query.has_valid_param("notetext"):
notetext = search_query.get_value("notetext").lower()
query = query.join(Note, GxdHTExperiment.notes).join(NoteChunk, Note.chunks).filter(db.func.lower(NoteChunk.note).like(notetext))
# The next portition of code will break if aliasing of gxd_experiment is used
if search_query.has_valid_param("experiment_variables"):
experiment_variables = search_query.get_value("experiment_variables")
# Or query
#search_list = []
#for var in experiment_variables:
# search_list.append(var["_term_key"])
# query = query.join(GxdHTExperimentVariable, GxdHTExperiment.experiment_variables).filter(GxdHTExperimentVariable._term_key.in_(search_list))
# And Query
for var in experiment_variables:
sq = db.session.query(GxdHTExperimentVariable)
sq = sq.filter(GxdHTExperimentVariable._term_key == var["_term_key"])
sq = sq.filter(GxdHTExperimentVariable._experiment_key == GxdHTExperiment._experiment_key)
sq = sq.correlate(GxdHTExperiment)
query = query.filter(sq.exists())
query = query.order_by(GxdHTExperiment.primaryid.asc())
return query