コード例 #1
0
ファイル: reference_dao.py プロジェクト: mgijax/mgipython
 def get_by_jnum_id(self, jnum_id):
     """
     Return a reference by jnum_id
     """
     jnum_id = jnum_id.upper()
     
     sub_ref = db.aliased(Reference)
     accession_model = db.aliased(Accession)
     
     sq = db.session.query(sub_ref)
     sq = sq.join(accession_model, sub_ref.jnumid_object)
     sq = sq.filter(accession_model.accid==jnum_id)
     sq = sq.filter(sub_ref._refs_key==Reference._refs_key)
     sq = sq.correlate(Reference)
     
     
     query = Reference.query
     
     return query.filter( sq.exists() ).first()
コード例 #2
0
ファイル: vocterm_dao.py プロジェクト: mgijax/mgipython
 def get_by_primary_id(self, id):
     """
     Returns a VocTerm object using its primary ID 
     (Not necessarily an MGI ID, could be MP: or GO:, etc
     """
     id = id.upper()
     #return VocTerm.query.filter_by(primaryid=id).first()
     accAlias = db.aliased(Accession)
     return VocTerm.query.join(accAlias, VocTerm.primaryid_object) \
             .filter(accAlias.accid==id).first()
コード例 #3
0
ファイル: gxdresult_dao.py プロジェクト: mgijax/mgipython
    def _build_search_query(self, search_query):
        """
        Search Result by fields:
            marker_id
            refs_id
            direct_structure_id
        """
        query = Result.query

        query = query.join(Result.marker)
        query = query.join(Result.assay)
        emapa_structure = db.aliased(VocTerm)
        query = query.join(emapa_structure, Result.structure)
    
        if search_query.has_valid_param("marker_id"):
            
            marker_id = search_query.get_value("marker_id")
            
            marker_accession = db.aliased(Accession)
            sub_result = db.aliased(Result)
            sq = db.session.query(sub_result) \
                    .join(sub_result.marker) \
                    .join(marker_accession, Marker.mgiid_object) \
                    .filter(marker_accession.accid==marker_id) \
                    .filter(sub_result._expression_key==Result._expression_key) \
                    .correlate(Result)
                
            query = query.filter(
                    sq.exists()
            )
    
        has_refs_id_query = search_query.has_valid_param("refs_id")
        if has_refs_id_query:
            
            search_query.get_value("refs_id")
            
            reference_accession = db.aliased(Accession)
            sub_result = db.aliased(Result)
            sq = db.session.query(sub_result) \
                    .join(sub_result.reference) \
                    .join(reference_accession, Reference.jnumid_object) \
                    .filter(reference_accession.accid==refs_id) \
                    .filter(sub_result._expression_key==Result._expression_key) \
                    .correlate(Result)
                
            query = query.filter(
                    sq.exists()
            )
            
        if search_query.has_valid_param("direct_structure_id"):
            
            direct_structure_id = search_query.get_value("direct_structure_id")
            
            # I.e. an EMAPA ID
            
            if "EMAPS" in direct_structure_id:
                
                # convert EMAPS to EMAPA + stage
                stage = int(direct_structure_id[-2:])
                direct_structure_id = direct_structure_id[:-2].replace("EMAPS","EMAPA")
                
                query = query.filter(Result._stage_key==stage)
            
            structure_accession = db.aliased(Accession)
            sub_result = db.aliased(Result)
            sq = db.session.query(sub_result) \
                    .join(sub_result.structure) \
                    .join(structure_accession, VocTerm.primaryid_object) \
                    .filter(structure_accession.accid==direct_structure_id) \
                    .filter(sub_result._expression_key==Result._expression_key) \
                    .correlate(Result)
                
            query = query.filter(
                    sq.exists()
            )
    
    
        # specific sorts requested by GXD
        if has_refs_id_query:
                # sort for reference summary
                # 1) Structure by TS then alpha
                # 2) Gene symbol
                # 3) assay type
                # 4) assay MGI ID
                # 5) Specimen label
                query = query.outerjoin(Result.specimen)
                query = query.order_by(Result.isrecombinase,
                                       Result._stage_key,
                                       emapa_structure.term,
                                       Marker.symbol,
                                       Assay.assaytype_seq,
                                       Assay.mgiid,
                                       Specimen.specimenlabel
                                       )
        else:
                # default sort for all other types of summaries
                query = query.order_by(Result.isrecombinase, 
                               Marker.symbol, 
                               Assay.assaytype_seq,
                               Result._stage_key, 
                               emapa_structure.term, 
                               Result.expressed)
        
        return query
コード例 #4
0
ファイル: gxdindex_dao.py プロジェクト: mgijax/mgipython
    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
コード例 #5
0
ファイル: reference_dao.py プロジェクト: mgijax/mgipython
 def search(self, accids=None, 
                  journal=None, 
                  title=None,
                  authors=None, 
                  primeAuthor=None, 
                  volume=None, 
                  year=None, 
                  marker_id=None, 
                  allele_id=None,
                  limit=None):
     """
     Search references by
         accids,
         journal,
         title,
         authors,
         primAuthor,
         volume,
         year,
         marker_id,
         allele_id
     """
     
     query = Reference.query
     
     if authors:
         authors = authors.lower()
         query = query.filter(
             db.func.lower(Reference.authors).like(authors),
         )
 
     if primeAuthor:
         primeAuthor = primeAuthor.lower()
         query = query.filter(
             db.func.lower(Reference._primary).like(primeAuthor),
         )
 
     if journal:
         journal = journal.lower()
         query = query.filter(
             db.func.lower(Reference.journal).like(journal),
         )
         
     if title:
         title = title.lower()
         query = query.filter(
             db.func.lower(Reference.title).like(title),
         )
 
     if volume:
         volume = volume.lower()
         query = query.filter(db.func.lower(Reference.vol)==volume)
 
     if year:
         query = query.filter(Reference.year==int(year))
 
     if marker_id:
         marker_accession = db.aliased(Accession)
         sub_reference = db.aliased(Reference)
         sq = db.session.query(sub_reference) \
                 .join(sub_reference.all_markers) \
                 .join(marker_accession, Marker.mgiid_object) \
                 .filter(marker_accession.accid==marker_id) \
                 .filter(sub_reference._refs_key==Reference._refs_key) \
                 .correlate(Reference)
                 
         query = query.filter(
                 sq.exists()     
         )
         
     if allele_id:
         allele_accession = db.aliased(Accession)
         sub_reference = db.aliased(Reference)
         sq = db.session.query(sub_reference) \
                 .join(sub_reference.explicit_alleles) \
                 .join(allele_accession, Allele.mgiid_object) \
                 .filter(allele_accession.accid==allele_id) \
                 .filter(sub_reference._refs_key==Reference._refs_key) \
                 .correlate(Reference)
             
         query = query.filter(
                 sq.exists()
         )
         
         
     if accids:
         # split and prep the IDs
         accids = accids.lower()
         accidsToSearch = splitCommaInput(accids)
         
         jnum_accession = db.aliased(Accession)
         sub_ref1 = db.aliased(Reference)
         
         ref_sq = db.session.query(sub_ref1) \
                 .join(jnum_accession, sub_ref1.jnumid_object) \
                 .filter(db.func.lower(jnum_accession.accid).in_(accidsToSearch)) \
                 .filter(sub_ref1._refs_key==Reference._refs_key) \
                 .correlate(Reference)
                 
         pmed_accession = db.aliased(Accession)
         sub_ref2 = db.aliased(Reference)
                 
         pmed_sq = db.session.query(sub_ref2) \
                 .join(pmed_accession, sub_ref2.pubmedid_object) \
                 .filter(db.func.lower(pmed_accession.accid).in_(accidsToSearch)) \
                 .filter(sub_ref2._refs_key==Reference._refs_key) \
                 .correlate(Reference)
         
         query1 = query.filter(ref_sq.exists())
         query2 = query.filter(pmed_sq.exists())
         
         query = query1.union(query2)
                         
     # setting sort
     query = query.order_by(Reference._refs_key.desc())
 
     # setting limit on number of returned references
     if limit:
         query = query.limit(limit) 
                    
     references = query.all()
        
     return references
コード例 #6
0
ファイル: vocterm_dao.py プロジェクト: mgijax/mgipython
    def _build_search_query(self, search_query):

        query = VocTerm.query
        
        if search_query.has_valid_param("isobsolete"):
            isobsolete = search_query.get_value("isobsolete")
            query = query.filter(VocTerm.isobsolete==isobsolete)
        
        if search_query.has_valid_param("_vocab_key"):
            query = query.filter(VocTerm._vocab_key==search_query.get_value("_vocab_key"))

        if search_query.has_valid_param("_term_key"):
            query = query.filter(VocTerm._term_key==search_query.get_value("_term_key"))
            
            
        if search_query.has_valid_param("term"):
            
            term = search_query.get_value("term")
            term = term.lower()
            
            query = query.filter(db.func.lower(VocTerm.term)==term)
            
            
        if search_query.has_valid_param("vocab_name"):
        
            vocab_name = search_query.get_value("vocab_name")
            vocab_name = vocab_name.lower()
            
            vocab_alias = db.aliased(Vocab)
            query = query.join(vocab_alias, VocTerm.vocab)
            query = query.filter(db.func.lower(vocab_alias.name)==vocab_name)
            
            
            
        # Specific to EMAPA Browser stage searching
        if search_query.has_valid_param('stageSearch'):
            
            stageSearch = search_query.get_value('stageSearch')
            stages = emapaStageParser(stageSearch)
            
            if stages:
                
                stages = [int(stage) for stage in stages]
                
                emapa_alias = db.aliased(VocTermEMAPA)
                emaps_alias = db.aliased(VocTermEMAPS)
                sub_term = db.aliased(VocTerm)
                
                sq = db.session.query(sub_term) \
                    .join(emapa_alias, sub_term.emapa_info) \
                    .join(emaps_alias, emapa_alias.emaps_infos) \
                    .filter(emaps_alias._stage_key.in_(stages)) \
                    .filter(sub_term._term_key==VocTerm._term_key) \
                    .correlate(VocTerm)
                
                query = query.filter(sq.exists())
        
        
        # Specific to EMAPA Browser term searching
        if search_query.has_valid_param('termSearch'):
            
            termSearch = search_query.get_value('termSearch')
            termSearch = termSearch.lower()
            termsToSearch = splitSemicolonInput(termSearch)
            
            # query IDs, terms, and synonyms then UNION all
            
            # search accession ID
            accession_alias = db.aliased(Accession)
            sub_term1 = db.aliased(VocTerm)
            
            id_sq = db.session.query(sub_term1) \
                    .join(accession_alias, sub_term1.all_accession_ids) \
                    .filter(db.func.lower(accession_alias.accid).in_(termsToSearch)) \
                    .filter(sub_term1._term_key==VocTerm._term_key) \
                    .correlate(VocTerm)
            
            # search terms
            sub_term2 = db.aliased(VocTerm)
            term_sq = db.session.query(sub_term2) \
                    .filter(db.or_(
                                   db.func.lower(VocTerm.term).like(term) for term in termsToSearch \
                            )) \
                    .filter(sub_term2._term_key==VocTerm._term_key) \
                    .correlate(VocTerm)
            
            # search synonyms
            synonym_alias = db.aliased(Synonym)
            sub_term3 = db.aliased(VocTerm)
                    
            synonym_sq = db.session.query(sub_term3) \
                    .join(synonym_alias, sub_term3.synonyms) \
                    .filter(db.or_(
                                   db.func.lower(synonym_alias.synonym).like(term) for term in termsToSearch \
                            )) \
                    .filter(sub_term3._term_key==VocTerm._term_key) \
                    .correlate(VocTerm)
            
            # perform union
            query1 = query.filter(id_sq.exists())
            query2 = query.filter(term_sq.exists())
            query3 = query.filter(synonym_sq.exists())
            query = query1.union(query2).union(query3)


        # handle sorts
        sorts = []
        if len(search_query.sorts) > 0:
            
            for sort_name in search_query.sorts:
                
                if sort_name == "sequencenum":
                    sorts.append(VocTerm.sequencenum.asc())
                    
                elif sort_name == "term":
                    sorts.append(VocTerm.term.asc())
                    
        else:
            sorts.append(VocTerm.sequencenum.asc())
            
        query = query.order_by(*sorts)

        return query