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