Exemple #1
0
    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