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