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