Beispiel #1
0
def getModelByMGIIDSubQuery(modelClass, mgiid, mgitypeKeyAttr='_mgitype_key'):
    """
    Class must have _mgitype_key class attribute
    returns a subquery that can be filter as an exists clause
    
    E.g.
    subQuery = getModelByMGIIDSubQuery(Marker, 'MGI:12345')
    marker = Marker.query.filter( subQuery.exists() ).first()
    """
    sub_model = db.aliased(modelClass)
    accession_model = db.aliased(Accession)

    # get primary_key name
    pkName = class_mapper(modelClass).primary_key[0].name

    # get the _mgitype_key
    _mgitype_key = getattr(modelClass, mgitypeKeyAttr)

    sq = db.session.query(sub_model)
    sq = sq.join(
        accession_model,
        db.and_(accession_model.preferred == 1,
                accession_model._logicaldb_key == 1,
                accession_model.prefixpart == 'MGI:',
                accession_model._object_key == getattr(sub_model, pkName),
                accession_model._mgitype_key == _mgitype_key))
    sq = sq.filter(accession_model.accid == mgiid)
    sq = sq.filter(getattr(sub_model, pkName) == getattr(modelClass, pkName))
    sq = sq.correlate(modelClass)

    return sq
Beispiel #2
0
def getModelByMGIIDSubQuery(modelClass, mgiid, mgitypeKeyAttr='_mgitype_key'):
    """
    Class must have _mgitype_key class attribute
    returns a subquery that can be filter as an exists clause
    
    E.g.
    subQuery = getModelByMGIIDSubQuery(Marker, 'MGI:12345')
    marker = Marker.query.filter( subQuery.exists() ).first()
    """
    sub_model = db.aliased(modelClass)
    accession_model = db.aliased(Accession)
    
    # get primary_key name
    pkName = class_mapper(modelClass).primary_key[0].name
    
    # get the _mgitype_key
    _mgitype_key = getattr(modelClass, mgitypeKeyAttr)
    
    sq = db.session.query(sub_model)
    sq = sq.join(accession_model, 
                db.and_(
                     accession_model.preferred==1,
                     accession_model._logicaldb_key==1,
                     accession_model.prefixpart=='MGI:',
                     accession_model._object_key==getattr(sub_model, pkName),
                     accession_model._mgitype_key==_mgitype_key
                ))
    sq = sq.filter(accession_model.accid==mgiid)
    sq = sq.filter(getattr(sub_model, pkName)==getattr(modelClass, pkName))
    sq = sq.correlate(modelClass)
    
    return sq

    
    
Beispiel #3
0
def searchAntibodies(marker_id=None,
                 refs_id=None):
    """
    Perform search for Antibody records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by Marker.symbol, Antibody.antibodyname, Antibody.mgiid
    """
    
    query = Antibody.query
    
   
    if refs_id:
        
        reference_accession = db.aliased(Accession)
        sub_antibody = db.aliased(Antibody)
        sq = db.session.query(sub_antibody) \
                .join(sub_antibody.references) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_antibody._antibody_key==Antibody._antibody_key) \
                .correlate(Antibody)
            
        query = query.filter(
                sq.exists()
        )
        
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_antibody = db.aliased(Antibody)
        sq = db.session.query(sub_antibody) \
                .join(sub_antibody.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_antibody._antibody_key==Antibody._antibody_key) \
                .correlate(Antibody)
            
        query = query.filter(
                sq.exists()
        )
        
    antibodies = query.all()
    
    # load data needed on summary page
    batchLoadAttribute(antibodies, 'antigen')
    batchLoadAttribute(antibodies, 'antigen.source')
    batchLoadAttribute(antibodies, 'markers')
    batchLoadAttribute(antibodies, 'references')
    
    # sort antibodies in python, because we need the first marker symbol
    # and I'm not sure how to do that in SQLAlchemy
    _sort_antibodies(antibodies)
    
    return antibodies
Beispiel #4
0
def searchAntibodies(marker_id=None,
                 refs_id=None):
    """
    Perform search for Antibody records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by Marker.symbol, Antibody.antibodyname, Antibody.mgiid
    """
    
    query = Antibody.query
    
   
    if refs_id:
        
        reference_accession = db.aliased(Accession)
        sub_antibody = db.aliased(Antibody)
        sq = db.session.query(sub_antibody) \
                .join(sub_antibody.references) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_antibody._antibody_key==Antibody._antibody_key) \
                .correlate(Antibody)
            
        query = query.filter(
                sq.exists()
        )
        
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_antibody = db.aliased(Antibody)
        sq = db.session.query(sub_antibody) \
                .join(sub_antibody.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_antibody._antibody_key==Antibody._antibody_key) \
                .correlate(Antibody)
            
        query = query.filter(
                sq.exists()
        )
        
    antibodies = query.all()
    
    # load data needed on summary page
    batchLoadAttribute(antibodies, 'antigen')
    batchLoadAttribute(antibodies, 'antigen.source')
    batchLoadAttribute(antibodies, 'markers')
    batchLoadAttribute(antibodies, 'references')
    
    # sort antibodies in python, because we need the first marker symbol
    # and I'm not sure how to do that in SQLAlchemy
    _sort_antibodies(antibodies)
    
    return antibodies
Beispiel #5
0
def searchProbes(marker_id=None,
                 refs_id=None,
                 probe_name=None,
                 segmenttypes=None,
                 limit=None):
    """
    Perform search for Probe records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by Probe.name
    """

    query = Probe.query

    if segmenttypes:
        segtypeAlias = db.aliased(VocTerm)

        query = query.join(segtypeAlias, Probe.segmenttype_obj) \
                .filter(segtypeAlias.term.in_(segmenttypes))

    if refs_id:

        ref_accession = db.aliased(Accession)
        sub_probe = db.aliased(Probe)

        sq = db.session.query(sub_probe) \
                .join(sub_probe.references) \
                .join(ref_accession, Reference.jnumid_object) \
                .filter(ref_accession.accid==refs_id) \
                .filter(sub_probe._probe_key==Probe._probe_key) \
                .correlate(Probe)

        query = query.filter(sq.exists())

    if marker_id:

        marker_accession = db.aliased(Accession)
        sub_probe = db.aliased(Probe)
        sq = db.session.query(sub_probe) \
                .join(sub_probe.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_probe._probe_key==Probe._probe_key) \
                .correlate(Probe)

        query = query.filter(sq.exists())

    if probe_name:
        probe_name = probe_name.lower()

        probeAlias = db.aliased(ProbeAlias)
        probeRef = db.aliased(ProbeReferenceCache)
        sub_probe = db.aliased(Probe)

        alias_sq = db.session.query(sub_probe) \
            .join(probeRef, sub_probe._probe_reference_caches) \
            .join(probeAlias, probeRef.probe_aliases) \
            .filter(db.func.lower(probeAlias.alias).like(probe_name)) \
            .filter(sub_probe._probe_key==Probe._probe_key) \
            .correlate(Probe)

        query1 = query.filter(db.func.lower(Probe.name).like(probe_name))
        query2 = query.filter(alias_sq.exists())

        query = query1.union(query2)

    query = query.order_by(Probe.name)

    if limit:
        query = query.limit(limit)

    probes = query.all()

    # batch load some related data needed on summary page
    batchLoadAttribute(probes, 'source')
    batchLoadAttribute(probes, 'markers')
    batchLoadAttribute(probes, 'references')
    batchLoadAttribute(probes, '_probe_marker_caches')
    batchLoadAttribute(probes, '_probe_reference_caches')
    batchLoadAttribute(probes, '_probe_reference_caches.probe_aliases')
    batchLoadAttribute(probes, 'derivedfrom_probe')

    #     probe_assocs = []
    #     for probe in probes:
    #         probe_assocs.extend(probe.probe_marker_caches)
    #     batchLoadAttribute(probe_assocs, 'marker')

    return probes
Beispiel #6
0
def searchMarkers(nomen=None, 
                  refs_id=None, 
                  featuretypes=None, 
                  limit=None):
    """
    Perform search for Marker records by various parameters
    e.g. nomen, _refs_key
    
    ordered by Marker.symbol
    """
    
    query = Marker.query.filter_by(_organism_key=1)
            
    if refs_id:
        reference_accession = db.aliased(Accession)
        sub_marker = db.aliased(Marker)
        sq = db.session.query(sub_marker) \
                .join(sub_marker.all_references) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_marker._marker_key==Marker._marker_key) \
                .correlate(Marker)
                
        query = query.filter(
                sq.exists()     
        )
        
    if featuretypes:
        
        ft_vocterm = db.aliased(VocTerm)
        sub_marker = db.aliased(Marker)
        sq = db.session.query(sub_marker) \
                .join(ft_vocterm, sub_marker.featuretype_vocterms) \
                .filter(
                    db.or_(
                       ft_vocterm.term.in_(featuretypes),
                       ft_vocterm.ancestor_vocterms.any(VocTerm.term.in_(featuretypes))
                    )
                ) \
                .filter(sub_marker._marker_key==Marker._marker_key) \
                .correlate(Marker)
                
        query = query.filter(
                sq.exists()
        )
        
        
    if nomen:
        nomen = nomen.lower()
        # query Marker symbol, name, synonyms
#         query = query.filter(
#                 db.or_(db.func.lower(Marker.symbol).like(nomen),
#                        db.func.lower(Marker.name).like(nomen),
#                        Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen))
#                        )
#         ) 
        
        query1 = query.filter(db.func.lower(Marker.symbol).like(nomen))
        query2 = query.filter(db.func.lower(Marker.name).like(nomen))
        query3 = query.filter(Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen)))
        
        query = query1.union(query2).union(query3)
     
    query = query.order_by(Marker.markerstatus, Marker.symbol)
    
    if limit:
        query = query.limit(limit)
        
    markers = query.all()
    
    # batch load some related data needed on summary page
    batchLoadAttribute(markers, 'synonyms')
    #batchLoadAttribute(markers, 'secondary_mgiids')
    batchLoadAttribute(markers, 'featuretype_vocterms')
    
    return markers
Beispiel #7
0
def searchMarkers(nomen=None, refs_id=None, featuretypes=None, limit=None):
    """
    Perform search for Marker records by various parameters
    e.g. nomen, _refs_key
    
    ordered by Marker.symbol
    """

    query = Marker.query.filter_by(_organism_key=1)

    if refs_id:
        reference_accession = db.aliased(Accession)
        sub_marker = db.aliased(Marker)
        sq = db.session.query(sub_marker) \
                .join(sub_marker.all_references) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_marker._marker_key==Marker._marker_key) \
                .correlate(Marker)

        query = query.filter(sq.exists())

    if featuretypes:

        ft_vocterm = db.aliased(VocTerm)
        sub_marker = db.aliased(Marker)
        sq = db.session.query(sub_marker) \
                .join(ft_vocterm, sub_marker.featuretype_vocterms) \
                .filter(
                    db.or_(
                       ft_vocterm.term.in_(featuretypes),
                       ft_vocterm.ancestor_vocterms.any(VocTerm.term.in_(featuretypes))
                    )
                ) \
                .filter(sub_marker._marker_key==Marker._marker_key) \
                .correlate(Marker)

        query = query.filter(sq.exists())

    if nomen:
        nomen = nomen.lower()
        # query Marker symbol, name, synonyms
        #         query = query.filter(
        #                 db.or_(db.func.lower(Marker.symbol).like(nomen),
        #                        db.func.lower(Marker.name).like(nomen),
        #                        Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen))
        #                        )
        #         )

        query1 = query.filter(db.func.lower(Marker.symbol).like(nomen))
        query2 = query.filter(db.func.lower(Marker.name).like(nomen))
        query3 = query.filter(
            Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen)))

        query = query1.union(query2).union(query3)

    query = query.order_by(Marker.markerstatus, Marker.symbol)

    if limit:
        query = query.limit(limit)

    markers = query.all()

    # batch load some related data needed on summary page
    batchLoadAttribute(markers, 'synonyms')
    #batchLoadAttribute(markers, 'secondary_mgiids')
    batchLoadAttribute(markers, 'featuretype_vocterms')

    return markers
Beispiel #8
0
def searchProbes(marker_id=None,
                 refs_id=None, 
                 probe_name=None,
                 segmenttypes=None,
                 limit=None):
    """
    Perform search for Probe records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by Probe.name
    """
    
    query = Probe.query
    
    if segmenttypes:
        segtypeAlias = db.aliased(VocTerm)
        
        query = query.join(segtypeAlias, Probe.segmenttype_obj) \
                .filter(segtypeAlias.term.in_(segmenttypes))
        
   
    if refs_id:
        
        ref_accession = db.aliased(Accession)
        sub_probe = db.aliased(Probe)
        
        sq = db.session.query(sub_probe) \
                .join(sub_probe.references) \
                .join(ref_accession, Reference.jnumid_object) \
                .filter(ref_accession.accid==refs_id) \
                .filter(sub_probe._probe_key==Probe._probe_key) \
                .correlate(Probe)
        
        query = query.filter(
                sq.exists()
        )
        
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_probe = db.aliased(Probe)
        sq = db.session.query(sub_probe) \
                .join(sub_probe.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_probe._probe_key==Probe._probe_key) \
                .correlate(Probe)
            
        query = query.filter(
                sq.exists()
        )
        
        
    if probe_name:
        probe_name = probe_name.lower()
        
        probeAlias = db.aliased(ProbeAlias)
        probeRef = db.aliased(ProbeReferenceCache)
        sub_probe = db.aliased(Probe)
        
        alias_sq = db.session.query(sub_probe) \
            .join(probeRef, sub_probe._probe_reference_caches) \
            .join(probeAlias, probeRef.probe_aliases) \
            .filter(db.func.lower(probeAlias.alias).like(probe_name)) \
            .filter(sub_probe._probe_key==Probe._probe_key) \
            .correlate(Probe)
        
        query1 = query.filter(db.func.lower(Probe.name).like(probe_name))
        query2 = query.filter(alias_sq.exists())
        
        query = query1.union(query2)
   
            
    query = query.order_by(Probe.name)
    
    if limit:
        query = query.limit(limit)
        
    probes = query.all()
    
    # batch load some related data needed on summary page
    batchLoadAttribute(probes, 'source')
    batchLoadAttribute(probes, 'markers')
    batchLoadAttribute(probes, 'references')
    batchLoadAttribute(probes, '_probe_marker_caches')
    batchLoadAttribute(probes, '_probe_reference_caches')
    batchLoadAttribute(probes, '_probe_reference_caches.probe_aliases')
    batchLoadAttribute(probes, 'derivedfrom_probe')
    
#     probe_assocs = []
#     for probe in probes:
#         probe_assocs.extend(probe.probe_marker_caches)
#     batchLoadAttribute(probe_assocs, 'marker')
    
    return probes