Example #1
0
def searchExperiments(marker_id=None,
                  refs_id=None,
                  expttypes=None,
                  limit=None):
    """
    Perform search for MappingExperiment records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by MappingExperiment.expttype, _refs_key
    """
    
    query = MappingExperiment.query
    
    
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_experiment = db.aliased(MappingExperiment)
        sq = db.session.query(sub_experiment) \
                .join(sub_experiment.marker_assocs) \
                .join(ExperimentMarkerAssoc.marker) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_experiment._expt_key==MappingExperiment._expt_key) \
                .correlate(MappingExperiment)
            
        query = query.filter(
                sq.exists()
        )
        
        
    if refs_id:
        
        refs_accession = db.aliased(Accession)
        sub_experiment = db.aliased(MappingExperiment)
        sq = db.session.query(sub_experiment) \
                .join(sub_experiment.reference) \
                .join(refs_accession, Reference.jnumid_object) \
                .filter(refs_accession.accid==refs_id) \
                .filter(sub_experiment._expt_key==MappingExperiment._expt_key) \
                .correlate(MappingExperiment)
            
        query = query.filter(
                sq.exists()
        )
            
    if expttypes:
        query = query.filter(MappingExperiment.expttype.in_(MappingExperiment.VALID_EXPTTYPES))
            
    query = query.order_by(MappingExperiment.expttype, MappingExperiment._refs_key, MappingExperiment.chromosome)
    
    if limit:
        query = query.limit(limit)
        
    experiments = query.all()
    
    # load any necessary data for summary
    batchLoadAttribute(experiments, 'reference')
    
    return experiments
Example #2
0
def searchImages(refs_id=None, limit=None):

    images = []
    expressionImageClass = "6481781"

    query = Image.query

    # query only GXD Images
    query = query.filter(Image._imageclass_key == expressionImageClass)

    # for this ref ID, gather images
    if refs_id:
        reference_accession = db.aliased(Accession)
        sub_image = db.aliased(Image)
        sq = db.session.query(sub_image) \
                .join(sub_image.reference) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_image._refs_key==Image._refs_key) \
                .correlate(Image)

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

    query = query.order_by(Image.figurelabel)

    images = query.all()

    return images
Example #3
0
def searchImages(refs_id=None, limit=None):

    images = []
    expressionImageClass = "6481781"

    query = Image.query

    # query only GXD Images
    query = query.filter(Image._imageclass_key==expressionImageClass)

            
    # for this ref ID, gather images
    if refs_id:
        reference_accession = db.aliased(Accession)
        sub_image = db.aliased(Image)
        sq = db.session.query(sub_image) \
                .join(sub_image.reference) \
                .join(reference_accession, Reference.jnumid_object) \
                .filter(reference_accession.accid==refs_id) \
                .filter(sub_image._refs_key==Image._refs_key) \
                .correlate(Image)
                
        query = query.filter(
                sq.exists()     
        )

    query = query.order_by(Image.figurelabel)

    images = query.all()
     

    return images
Example #4
0
def searchExperiments(marker_id=None,
                      refs_id=None,
                      expttypes=None,
                      limit=None):
    """
    Perform search for MappingExperiment records by various parameters
    e.g. marker_id, _refs_id
    
    ordered by MappingExperiment.expttype, _refs_key
    """

    query = MappingExperiment.query

    if marker_id:

        marker_accession = db.aliased(Accession)
        sub_experiment = db.aliased(MappingExperiment)
        sq = db.session.query(sub_experiment) \
                .join(sub_experiment.marker_assocs) \
                .join(ExperimentMarkerAssoc.marker) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_experiment._expt_key==MappingExperiment._expt_key) \
                .correlate(MappingExperiment)

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

    if refs_id:

        refs_accession = db.aliased(Accession)
        sub_experiment = db.aliased(MappingExperiment)
        sq = db.session.query(sub_experiment) \
                .join(sub_experiment.reference) \
                .join(refs_accession, Reference.jnumid_object) \
                .filter(refs_accession.accid==refs_id) \
                .filter(sub_experiment._expt_key==MappingExperiment._expt_key) \
                .correlate(MappingExperiment)

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

    if expttypes:
        query = query.filter(
            MappingExperiment.expttype.in_(MappingExperiment.VALID_EXPTTYPES))

    query = query.order_by(MappingExperiment.expttype,
                           MappingExperiment._refs_key,
                           MappingExperiment.chromosome)

    if limit:
        query = query.limit(limit)

    experiments = query.all()

    # load any necessary data for summary
    batchLoadAttribute(experiments, 'reference')

    return experiments
Example #5
0
def searchAlleles(refs_id=None, 
                  marker_id=None,
                  assay_id=None, 
                  limit=None):
    """
    Perform search for Alleles
    """
    query = Allele.query
    
            
    if assay_id:
        query = query.filter(
                Allele.assays.any(Assay.mgiid==assay_id)
        )
  
    if marker_id:
        query = query.join(Allele.marker)
        marker_accession = db.aliased(Accession)
        query = query.join(marker_accession, Marker.mgiid_object)
        query = query.filter(
                marker_accession.accid==marker_id
        )

    if refs_id:
        jnum_accession = db.aliased(Accession)
        sub_allele = db.aliased(Allele)
        sq = db.session.query(sub_allele) \
                .join(sub_allele.explicit_references) \
                .join(jnum_accession, Reference.jnumid_object) \
                .filter(jnum_accession.accid==refs_id) \
                .filter(sub_allele._allele_key==Allele._allele_key) \
                .correlate(Allele)
            
        query = query.filter(
                sq.exists()
        )
        
    query = query.order_by(Allele.transmission.desc(), Allele.status, Allele.symbol)
    
    if limit:
        query = query.limit(limit)
     
    alleles = query.all()
    
    # load attributes needed on summary
    batchLoadAttribute(alleles, "mp_annots")
    batchLoadAttribute(alleles, "disease_annots")
    batchLoadAttribute(alleles, "disease_annots_DO")
    batchLoadAttribute(alleles, "subtypes")
    batchLoadAttribute(alleles, "synonyms")
    
    return alleles
Example #6
0
def searchSpecimens(jnum=None, 
                 limit=None):
    """
    Search for assay records (specific to specimen summary)
    
    """
    
    # join Marker + Reference for the order by clause
    #query = query.join(Assay.reference)
    query = Specimen.query
    query = query.join(Specimen.assay)
    query = query.join(Assay.marker)

    if jnum:
        ref_accession = db.aliased(Accession)
        sub_specimen = db.aliased(Specimen)
        sq = db.session.query(sub_specimen) \
            .join(sub_specimen.assay) \
            .join(Assay.reference) \
            .join(ref_accession, Reference.jnumid_object) \
            .filter(ref_accession.accid==jnum) \
            .filter(sub_specimen._specimen_key==Specimen._specimen_key) \
            .correlate(Specimen)
    
        query = query.filter(
            sq.exists()
        )
            
    
    query = query.order_by(
	db.case(
	    # sort Cre assay types (key in 10,11)  last
	    ((Assay._assaytype_key==10, 2),
	    (Assay._assaytype_key==11, 2)), 
	    else_=1
        ).asc(), 
	Specimen.specimenlabel, 
	Marker.symbol, 
	Assay.mgiid)
    
    if limit:
        query = query.limit(limit)
        
    specimens = query.all()
        
    return specimens
Example #7
0
def searchAlleles(refs_id=None, marker_id=None, assay_id=None, limit=None):
    """
    Perform search for Alleles
    """
    query = Allele.query

    if assay_id:
        query = query.filter(Allele.assays.any(Assay.mgiid == assay_id))

    if marker_id:
        query = query.join(Allele.marker)
        marker_accession = db.aliased(Accession)
        query = query.join(marker_accession, Marker.mgiid_object)
        query = query.filter(marker_accession.accid == marker_id)

    if refs_id:
        jnum_accession = db.aliased(Accession)
        sub_allele = db.aliased(Allele)
        sq = db.session.query(sub_allele) \
                .join(sub_allele.explicit_references) \
                .join(jnum_accession, Reference.jnumid_object) \
                .filter(jnum_accession.accid==refs_id) \
                .filter(sub_allele._allele_key==Allele._allele_key) \
                .correlate(Allele)

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

    query = query.order_by(Allele.transmission.desc(), Allele.status,
                           Allele.symbol)

    if limit:
        query = query.limit(limit)

    alleles = query.all()

    # load attributes needed on summary
    batchLoadAttribute(alleles, "mp_annots")
    batchLoadAttribute(alleles, "disease_annots")
    batchLoadAttribute(alleles, "subtypes")
    batchLoadAttribute(alleles, "synonyms")

    return alleles
Example #8
0
def _loadUsernames():
    """
    Loads the master list of all usernames
        in MGI
        in format [name,]
    """

    userStatusAlias = db.aliased(VocTerm)
    userTypeAlias = db.aliased(VocTerm)

    # get all users
    users = MGIUser.query \
        .join(userTypeAlias, MGIUser.usertype_object) \
        .join(userStatusAlias, MGIUser.userstatus_object) \
        .filter(userTypeAlias.term.in_(['Curator','SE','PI','User Support'])) \
        .filter(userStatusAlias.term=='Active') \
        .order_by(MGIUser.name) \
        .all()

    return [u.name for u in users]
Example #9
0
def searchSequences(marker_id=None,
                  limit=None):
    """
    Perform search for Sequence records by marker_id
    
    ordered by Sequence._sequence_key
    """
    
    query = Sequence.query
    
    
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_seq = db.aliased(Sequence)
        sq = db.session.query(sub_seq) \
                .join(sub_seq.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_seq._sequence_key==Sequence._sequence_key) \
                .correlate(Sequence)
            
        query = query.filter(
                sq.exists()
        )
            
            
    query = query.order_by(Sequence._sequence_key)
    
    if limit:
        query = query.limit(limit)
        
    sequences = query.all()
    
    # load any necessary data for summary
    batchLoadAttribute(sequences, 'markers')
    batchLoadAttribute(sequences, 'accession_objects')
    batchLoadAttribute(sequences, 'source')
    batchLoadAttribute(sequences, 'source.strain')
    
    return sequences
Example #10
0
def searchSpecimens(jnum=None, limit=None):
    """
    Search for assay records (specific to specimen summary)
    
    """

    # join Marker + Reference for the order by clause
    #query = query.join(Assay.reference)
    query = Specimen.query
    query = query.join(Specimen.assay)
    query = query.join(Assay.marker)

    if jnum:
        ref_accession = db.aliased(Accession)
        sub_specimen = db.aliased(Specimen)
        sq = db.session.query(sub_specimen) \
            .join(sub_specimen.assay) \
            .join(Assay.reference) \
            .join(ref_accession, Reference.jnumid_object) \
            .filter(ref_accession.accid==jnum) \
            .filter(sub_specimen._specimen_key==Specimen._specimen_key) \
            .correlate(Specimen)

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

    query = query.order_by(
        db.case(
            # sort Cre assay types (key in 10,11)  last
            ((Assay._assaytype_key == 10, 2), (Assay._assaytype_key == 11, 2)),
            else_=1).asc(),
        Specimen.specimenlabel,
        Marker.symbol,
        Assay.mgiid)

    if limit:
        query = query.limit(limit)

    specimens = query.all()

    return specimens
Example #11
0
def _loadUsernames():
    """
    Loads the master list of all usernames
        in MGI
        in format [name,]
    """

    userStatusAlias = db.aliased(VocTerm)
    userTypeAlias = db.aliased(VocTerm)
    
    # get all users
    users = MGIUser.query \
        .join(userTypeAlias, MGIUser.usertype_object) \
        .join(userStatusAlias, MGIUser.userstatus_object) \
        .filter(userTypeAlias.term.in_(['Curator','SE','PI','User Support'])) \
        .filter(userStatusAlias.term=='Active') \
        .order_by(MGIUser.name) \
        .all()
    
    
    return [u.name for u in users]
    
Example #12
0
def searchSequences(marker_id=None, limit=None):
    """
    Perform search for Sequence records by marker_id
    
    ordered by Sequence._sequence_key
    """

    query = Sequence.query

    if marker_id:

        marker_accession = db.aliased(Accession)
        sub_seq = db.aliased(Sequence)
        sq = db.session.query(sub_seq) \
                .join(sub_seq.markers) \
                .join(marker_accession, Marker.mgiid_object) \
                .filter(marker_accession.accid==marker_id) \
                .filter(sub_seq._sequence_key==Sequence._sequence_key) \
                .correlate(Sequence)

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

    query = query.order_by(Sequence._sequence_key)

    if limit:
        query = query.limit(limit)

    sequences = query.all()

    # load any necessary data for summary
    batchLoadAttribute(sequences, 'markers')
    batchLoadAttribute(sequences, 'accession_objects')
    batchLoadAttribute(sequences, 'source')
    batchLoadAttribute(sequences, 'source.strain')

    return sequences
Example #13
0
def searchIndexRecords(marker_id=None,
                       refs_id=None,
                       age=None,
                       assay_type=None,
                       limit=None):

    # results to be returned
    results = []

    query = GxdIndexRecord.query

    query = query.join(GxdIndexRecord.marker)
    #query = query.join(GxdIndexRecord.reference)

    if marker_id:

        marker_accession = db.aliased(Accession)
        sub_result = db.aliased(GxdIndexRecord)
        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._index_key==GxdIndexRecord._index_key) \
                .correlate(GxdIndexRecord)

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

    if refs_id:

        reference_accession = db.aliased(Accession)
        sub_result = db.aliased(GxdIndexRecord)
        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._index_key==GxdIndexRecord._index_key) \
                .correlate(GxdIndexRecord)

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

    if age:
        query = query.filter(
            GxdIndexRecord.indexstages.any(GxdIndexStage.stageid == age))

    if assay_type:
        query = query.filter(
            GxdIndexRecord.indexstages.any(
                GxdIndexStage.indexassay == assay_type))

    query = query.order_by(Marker.symbol, GxdIndexRecord._index_key)

    if limit:
        query = query.limit(limit)

    results = query.all()

    batchLoadAttribute(results, 'marker')
    batchLoadAttribute(results, 'reference')
    batchLoadAttribute(results, 'indexstages')

    return results
Example #14
0
def _buildResultQuery(marker_id=None, 
                  refs_id=None, 
                  direct_structure_id=None):
    """
    Build query statement for GXD expression results
    """
    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 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()
        )

    if 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 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 refs_id:
            # 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
Example #15
0
def getVocTermByPrimaryID(id):
    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()
Example #16
0
def _batchLoadAttribute(objects, attribute, batchSize=100):
    """
	Does not accept dot object notation
	Loads only a single attribute for each object
	"""

    if objects:
        refObject = objects[0]
        # reflect some of the necessary sqlalchemy configuration
        # original object model Class
        entity = refObject.__mapper__.entity
        # primary key names
        pkNames = [pk.key for pk in refObject.__mapper__.primary_key]
        # primary keys property class
        pkAttributes = [getattr(entity, pkName) for pkName in pkNames]
        # attribute property class
        loadAttribute = getattr(entity, attribute)
        # attribute entity class
        attributeClass = loadAttribute.property.mapper.entity
        # any attibute order_by clause
        order_by = loadAttribute.property.order_by
        # does relationship use list or single reference?
        uselist = loadAttribute.property.uselist

        # do alias if attributeClass == entity class
        useAlias = attributeClass == entity
        if useAlias:
            attributeClass = db.aliased(loadAttribute.property.mapper.entity)
            aliased_order = []
            if order_by:
                for order in order_by:
                    aliased_order.append(getattr(attributeClass, order.name))
            order_by = aliased_order

        #app.logger.debug('pkeys = %s' % pkNames)
        #app.logger.debug('pkAttr = %s' % pkAttributes)

        for batch in batch_list(objects, batchSize):
            # gen lists of primary keys
            primaryKeyLists = [[getattr(o, pkName) for o in batch]
                               for pkName in pkNames]

            #app.logger.debug('pkLists = %s' % primaryKeyLists)

            # query second list with attribute loaded
            query = None
            if useAlias:
                query = entity.query.add_entity(attributeClass).join(
                    attributeClass, loadAttribute)
            else:
                query = entity.query.add_entity(attributeClass).join(
                    loadAttribute)

            # filter by every primary key on the object
            for idx in range(len(pkNames)):
                pkName = pkNames[idx]
                pkAttribute = pkAttributes[idx]
                primaryKeys = primaryKeyLists[idx]
                query = query.filter(pkAttribute.in_(primaryKeys))

            # defer everything but primary keys
            query = query.options(*defer_everything_but(entity, pkNames))

            # preserve original order by on the attribute relationship
            if order_by:
                query = query.order_by(*order_by)

            loadedObjects = query.all()

            # make a lookup to match on primary key
            loadedLookup = {}
            for loadedObject in loadedObjects:

                pkey = tuple(
                    [getattr(loadedObject[0], pkName) for pkName in pkNames])
                #app.logger.debug('found pkey = %s' % pkey)
                if uselist:
                    loadedLookup.setdefault(pkey, []).append(loadedObject[1])
                else:
                    loadedLookup[pkey] = loadedObject[1]

            # match any found attributes from the loaded set
            for object in batch:
                loadedAttr = []
                if not uselist:
                    loadedAttr = None

                pkey = tuple([getattr(object, pkName) for pkName in pkNames])
                if pkey in loadedLookup:
                    loadedAttr = loadedLookup[pkey]

                orm.attributes.set_committed_value(object, attribute,
                                                   loadedAttr)
Example #17
0
def searchIndexRecords(marker_id=None, 
                       refs_id=None,
                       age=None,
                       assay_type=None,
                       limit=None):

    # results to be returned
    results = []

    query = GxdIndexRecord.query

    query = query.join(GxdIndexRecord.marker)
    #query = query.join(GxdIndexRecord.reference)

            
    if marker_id:
        
        marker_accession = db.aliased(Accession)
        sub_result = db.aliased(GxdIndexRecord)
        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._index_key==GxdIndexRecord._index_key) \
                .correlate(GxdIndexRecord)
            
        query = query.filter(
                sq.exists()
        )

    if refs_id:
        
        reference_accession = db.aliased(Accession)
        sub_result = db.aliased(GxdIndexRecord)
        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._index_key==GxdIndexRecord._index_key) \
                .correlate(GxdIndexRecord)
            
        query = query.filter(
                sq.exists()
                )


    if age:
            query = query.filter(
                GxdIndexRecord.indexstages.any(
                        GxdIndexStage.stageid==age
                ))


    if assay_type:
            query = query.filter(
                GxdIndexRecord.indexstages.any(
                        GxdIndexStage.indexassay==assay_type
                ))
                    
    query = query.order_by(Marker.symbol, GxdIndexRecord._index_key)

    if limit:
            query = query.limit(limit)
        
    results = query.all()
    
    batchLoadAttribute(results, 'marker')
    batchLoadAttribute(results, 'reference')
    batchLoadAttribute(results, 'indexstages')

    return results
Example #18
0
def searchAssays(marker_id=None,
                 allele_id=None,
                 probe_id=None,
                 refs_id=None, 
                 antibody_id=None, 
                 limit=None):
    """
    Perform search for GXD Assay records by various parameters
    e.g. Marker nomen, Assay _refs_key
    
    ordered by Marker.symbol
    """
    
    query = Assay.query
    
    # join Marker + Reference for the order by clause
    query = query.join(Assay.marker)
    query = query.join(Assay.reference)
    
    if marker_id:
        # query Marker MGI ID
        query = query.filter(Marker.mgiid==marker_id)
        
    if allele_id:
        # query Allele MGI ID
        allele_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.alleles) \
                .join(allele_accession, Allele.mgiid_object) \
                .filter(allele_accession.accid==allele_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)
            
        query = query.filter(
                sq.exists()
        )
        
    if probe_id:
        # query Probe MGI ID
        probe_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.probeprep) \
                .join(ProbePrep.probe) \
                .join(probe_accession, Probe.mgiid_object) \
                .filter(probe_accession.accid==probe_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)
            
        query = query.filter(
                sq.exists()
        )

    if antibody_id:
        # query Antibody MGI ID
        antibody_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.antibodyprep) \
                .join(AntibodyPrep.antibody) \
                .join(antibody_accession, Antibody.mgiid_object) \
                .filter(antibody_accession.accid==antibody_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)

        query = query.filter(
                sq.exists()
        )
        
            
    if refs_id:
        query = query.filter(Reference.jnumid==refs_id)
            
    query = query.order_by(Marker.symbol, 
                           Assay.assaytype_seq, 
                           Reference.authors,
                           Assay.mgiid)
    
    if limit:
        query = query.limit(limit)
        
    assays = query.all()
    
    # batch load some related data needed on summary page
    batchLoadAttribute(assays, 'marker')
    batchLoadAttribute(assays, 'reference')
    
    return assays
    
    
Example #19
0
def searchAssays(marker_id=None,
                 allele_id=None,
                 probe_id=None,
                 refs_id=None,
                 antibody_id=None,
                 limit=None):
    """
    Perform search for GXD Assay records by various parameters
    e.g. Marker nomen, Assay _refs_key
    
    ordered by Marker.symbol
    """

    query = Assay.query

    # join Marker + Reference for the order by clause
    query = query.join(Assay.marker)
    query = query.join(Assay.reference)

    if marker_id:
        # query Marker MGI ID
        query = query.filter(Marker.mgiid == marker_id)

    if allele_id:
        # query Allele MGI ID
        allele_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.alleles) \
                .join(allele_accession, Allele.mgiid_object) \
                .filter(allele_accession.accid==allele_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)

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

    if probe_id:
        # query Probe MGI ID
        probe_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.probeprep) \
                .join(ProbePrep.probe) \
                .join(probe_accession, Probe.mgiid_object) \
                .filter(probe_accession.accid==probe_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)

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

    if antibody_id:
        # query Antibody MGI ID
        antibody_accession = db.aliased(Accession)
        sub_assay = db.aliased(Assay)
        sq = db.session.query(sub_assay) \
                .join(sub_assay.antibodyprep) \
                .join(AntibodyPrep.antibody) \
                .join(antibody_accession, Antibody.mgiid_object) \
                .filter(antibody_accession.accid==antibody_id) \
                .filter(sub_assay._assay_key==Assay._assay_key) \
                .correlate(Assay)

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

    if refs_id:
        query = query.filter(Reference.jnumid == refs_id)

    query = query.order_by(Marker.symbol, Assay.assaytype_seq,
                           Reference.authors, Assay.mgiid)

    if limit:
        query = query.limit(limit)

    assays = query.all()

    # batch load some related data needed on summary page
    batchLoadAttribute(assays, 'marker')
    batchLoadAttribute(assays, 'reference')

    return assays
Example #20
0
def _batchLoadAttribute(objects, attribute, batchSize=100):
	"""
	Does not accept dot object notation
	Loads only a single attribute for each object
	"""
	
	if objects:
		refObject = objects[0]
		# reflect some of the necessary sqlalchemy configuration
		# original object model Class
		entity = refObject.__mapper__.entity
		# primary key names
		pkNames = [pk.key for pk in refObject.__mapper__.primary_key]
		# primary keys property class
		pkAttributes = [getattr(entity, pkName) for pkName in pkNames]
		# attribute property class
		loadAttribute = getattr(entity, attribute)
		# attribute entity class
		attributeClass = loadAttribute.property.mapper.entity
		# any attibute order_by clause
		order_by = loadAttribute.property.order_by
		# does relationship use list or single reference?
		uselist = loadAttribute.property.uselist
		
		# do alias if attributeClass == entity class
		useAlias = attributeClass == entity
		if useAlias:
			attributeClass = db.aliased(loadAttribute.property.mapper.entity)
			aliased_order = []
			if order_by:
				for order in order_by:
					aliased_order.append(getattr(attributeClass, order.name))
			order_by = aliased_order
			
		#app.logger.debug('pkeys = %s' % pkNames)
		#app.logger.debug('pkAttr = %s' % pkAttributes)
		
		for batch in batch_list(objects, batchSize):
			# gen lists of primary keys
			primaryKeyLists = [[getattr(o, pkName) for o in batch] for pkName in pkNames] 
			
			#app.logger.debug('pkLists = %s' % primaryKeyLists)
			
			# query second list with attribute loaded
			query = None
			if useAlias:
				query = entity.query.add_entity(attributeClass).join(attributeClass, loadAttribute)
			else:
				query = entity.query.add_entity(attributeClass).join(loadAttribute)
				
			# filter by every primary key on the object
			for idx in range(len(pkNames)):
				pkName = pkNames[idx]
				pkAttribute = pkAttributes[idx]
				primaryKeys = primaryKeyLists[idx]
				query = query.filter(pkAttribute.in_(primaryKeys))
			
			# defer everything but primary keys
			query = query.options(*defer_everything_but(entity, pkNames))
			
			# preserve original order by on the attribute relationship
			if order_by:
				query = query.order_by(*order_by)
				
			loadedObjects =  query.all()
			
			# make a lookup to match on primary key
			loadedLookup = {}
			for loadedObject in loadedObjects:
				
				pkey = tuple([getattr(loadedObject[0], pkName) for pkName in pkNames])
				#app.logger.debug('found pkey = %s' % pkey)
				if uselist:
					loadedLookup.setdefault(pkey, []).append(loadedObject[1])
				else:
					loadedLookup[pkey] = loadedObject[1]
			
			
			# match any found attributes from the loaded set
			for object in batch:
				loadedAttr = []
				if not uselist:
					loadedAttr = None
					
				pkey = tuple([getattr(object, pkName) for pkName in pkNames])
				if pkey in loadedLookup:
					loadedAttr = loadedLookup[pkey]
					
				orm.attributes.set_committed_value(object, attribute, loadedAttr)
Example #21
0
def _buildResultQuery(marker_id=None,
                      refs_id=None,
                      direct_structure_id=None,
                      direct_celltype_id=None):
    """
    Build query statement for GXD expression results
    """
    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)
    cell_type = db.aliased(VocTerm)
    query = query.outerjoin(cell_type, Result.celltype)

    if 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())

    if 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 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())

    if direct_celltype_id:
        celltype_accession = db.aliased(Accession)
        sub_result = db.aliased(Result)
        sq = db.session.query(sub_result) \
                .join(sub_result.celltype) \
                .join(celltype_accession, VocTerm.primaryid_object) \
                .filter(celltype_accession.accid==direct_celltype_id) \
                .filter(sub_result._expression_key==Result._expression_key) \
                .correlate(Result)

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

    # specific sorts requested by GXD
    if refs_id:
        # 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
Example #22
0
def getVocTermByPrimaryID(id):
    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()