def getModelByMGIIDSubQuery(modelClass, mgiid, mgitypeKeyAttr='_mgitype_key'):
"""
Class must have _mgitype_key class attribute
returns a subquery that can be filter as an exists clause
E.g.
subQuery = getModelByMGIIDSubQuery(Marker, 'MGI:12345')
marker = Marker.query.filter( subQuery.exists() ).first()
"""
sub_model = db.aliased(modelClass)
accession_model = db.aliased(Accession)
# get primary_key name
pkName = class_mapper(modelClass).primary_key[0].name
# get the _mgitype_key
_mgitype_key = getattr(modelClass, mgitypeKeyAttr)
sq = db.session.query(sub_model)
sq = sq.join(
accession_model,
db.and_(accession_model.preferred == 1,
accession_model._logicaldb_key == 1,
accession_model.prefixpart == 'MGI:',
accession_model._object_key == getattr(sub_model, pkName),
accession_model._mgitype_key == _mgitype_key))
sq = sq.filter(accession_model.accid == mgiid)
sq = sq.filter(getattr(sub_model, pkName) == getattr(modelClass, pkName))
sq = sq.correlate(modelClass)
return sq
def getModelByMGIIDSubQuery(modelClass, mgiid, mgitypeKeyAttr='_mgitype_key'):
"""
Class must have _mgitype_key class attribute
returns a subquery that can be filter as an exists clause
E.g.
subQuery = getModelByMGIIDSubQuery(Marker, 'MGI:12345')
marker = Marker.query.filter( subQuery.exists() ).first()
"""
sub_model = db.aliased(modelClass)
accession_model = db.aliased(Accession)
# get primary_key name
pkName = class_mapper(modelClass).primary_key[0].name
# get the _mgitype_key
_mgitype_key = getattr(modelClass, mgitypeKeyAttr)
sq = db.session.query(sub_model)
sq = sq.join(accession_model,
db.and_(
accession_model.preferred==1,
accession_model._logicaldb_key==1,
accession_model.prefixpart=='MGI:',
accession_model._object_key==getattr(sub_model, pkName),
accession_model._mgitype_key==_mgitype_key
))
sq = sq.filter(accession_model.accid==mgiid)
sq = sq.filter(getattr(sub_model, pkName)==getattr(modelClass, pkName))
sq = sq.correlate(modelClass)
return sq
def searchAntibodies(marker_id=None,
refs_id=None):
"""
Perform search for Antibody records by various parameters
e.g. marker_id, _refs_id
ordered by Marker.symbol, Antibody.antibodyname, Antibody.mgiid
"""
query = Antibody.query
if refs_id:
reference_accession = db.aliased(Accession)
sub_antibody = db.aliased(Antibody)
sq = db.session.query(sub_antibody) \
.join(sub_antibody.references) \
.join(reference_accession, Reference.jnumid_object) \
.filter(reference_accession.accid==refs_id) \
.filter(sub_antibody._antibody_key==Antibody._antibody_key) \
.correlate(Antibody)
query = query.filter(
sq.exists()
)
if marker_id:
marker_accession = db.aliased(Accession)
sub_antibody = db.aliased(Antibody)
sq = db.session.query(sub_antibody) \
.join(sub_antibody.markers) \
.join(marker_accession, Marker.mgiid_object) \
.filter(marker_accession.accid==marker_id) \
.filter(sub_antibody._antibody_key==Antibody._antibody_key) \
.correlate(Antibody)
query = query.filter(
sq.exists()
)
antibodies = query.all()
# load data needed on summary page
batchLoadAttribute(antibodies, 'antigen')
batchLoadAttribute(antibodies, 'antigen.source')
batchLoadAttribute(antibodies, 'markers')
batchLoadAttribute(antibodies, 'references')
# sort antibodies in python, because we need the first marker symbol
# and I'm not sure how to do that in SQLAlchemy
_sort_antibodies(antibodies)
return antibodies
def searchAntibodies(marker_id=None,
refs_id=None):
"""
Perform search for Antibody records by various parameters
e.g. marker_id, _refs_id
ordered by Marker.symbol, Antibody.antibodyname, Antibody.mgiid
"""
query = Antibody.query
if refs_id:
reference_accession = db.aliased(Accession)
sub_antibody = db.aliased(Antibody)
sq = db.session.query(sub_antibody) \
.join(sub_antibody.references) \
.join(reference_accession, Reference.jnumid_object) \
.filter(reference_accession.accid==refs_id) \
.filter(sub_antibody._antibody_key==Antibody._antibody_key) \
.correlate(Antibody)
query = query.filter(
sq.exists()
)
if marker_id:
marker_accession = db.aliased(Accession)
sub_antibody = db.aliased(Antibody)
sq = db.session.query(sub_antibody) \
.join(sub_antibody.markers) \
.join(marker_accession, Marker.mgiid_object) \
.filter(marker_accession.accid==marker_id) \
.filter(sub_antibody._antibody_key==Antibody._antibody_key) \
.correlate(Antibody)
query = query.filter(
sq.exists()
)
antibodies = query.all()
# load data needed on summary page
batchLoadAttribute(antibodies, 'antigen')
batchLoadAttribute(antibodies, 'antigen.source')
batchLoadAttribute(antibodies, 'markers')
batchLoadAttribute(antibodies, 'references')
# sort antibodies in python, because we need the first marker symbol
# and I'm not sure how to do that in SQLAlchemy
_sort_antibodies(antibodies)
return antibodies
def searchProbes(marker_id=None,
refs_id=None,
probe_name=None,
segmenttypes=None,
limit=None):
"""
Perform search for Probe records by various parameters
e.g. marker_id, _refs_id
ordered by Probe.name
"""
query = Probe.query
if segmenttypes:
segtypeAlias = db.aliased(VocTerm)
query = query.join(segtypeAlias, Probe.segmenttype_obj) \
.filter(segtypeAlias.term.in_(segmenttypes))
if refs_id:
ref_accession = db.aliased(Accession)
sub_probe = db.aliased(Probe)
sq = db.session.query(sub_probe) \
.join(sub_probe.references) \
.join(ref_accession, Reference.jnumid_object) \
.filter(ref_accession.accid==refs_id) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query = query.filter(sq.exists())
if marker_id:
marker_accession = db.aliased(Accession)
sub_probe = db.aliased(Probe)
sq = db.session.query(sub_probe) \
.join(sub_probe.markers) \
.join(marker_accession, Marker.mgiid_object) \
.filter(marker_accession.accid==marker_id) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query = query.filter(sq.exists())
if probe_name:
probe_name = probe_name.lower()
probeAlias = db.aliased(ProbeAlias)
probeRef = db.aliased(ProbeReferenceCache)
sub_probe = db.aliased(Probe)
alias_sq = db.session.query(sub_probe) \
.join(probeRef, sub_probe._probe_reference_caches) \
.join(probeAlias, probeRef.probe_aliases) \
.filter(db.func.lower(probeAlias.alias).like(probe_name)) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query1 = query.filter(db.func.lower(Probe.name).like(probe_name))
query2 = query.filter(alias_sq.exists())
query = query1.union(query2)
query = query.order_by(Probe.name)
if limit:
query = query.limit(limit)
probes = query.all()
# batch load some related data needed on summary page
batchLoadAttribute(probes, 'source')
batchLoadAttribute(probes, 'markers')
batchLoadAttribute(probes, 'references')
batchLoadAttribute(probes, '_probe_marker_caches')
batchLoadAttribute(probes, '_probe_reference_caches')
batchLoadAttribute(probes, '_probe_reference_caches.probe_aliases')
batchLoadAttribute(probes, 'derivedfrom_probe')
# probe_assocs = []
# for probe in probes:
# probe_assocs.extend(probe.probe_marker_caches)
# batchLoadAttribute(probe_assocs, 'marker')
return probes
def searchMarkers(nomen=None,
refs_id=None,
featuretypes=None,
limit=None):
"""
Perform search for Marker records by various parameters
e.g. nomen, _refs_key
ordered by Marker.symbol
"""
query = Marker.query.filter_by(_organism_key=1)
if refs_id:
reference_accession = db.aliased(Accession)
sub_marker = db.aliased(Marker)
sq = db.session.query(sub_marker) \
.join(sub_marker.all_references) \
.join(reference_accession, Reference.jnumid_object) \
.filter(reference_accession.accid==refs_id) \
.filter(sub_marker._marker_key==Marker._marker_key) \
.correlate(Marker)
query = query.filter(
sq.exists()
)
if featuretypes:
ft_vocterm = db.aliased(VocTerm)
sub_marker = db.aliased(Marker)
sq = db.session.query(sub_marker) \
.join(ft_vocterm, sub_marker.featuretype_vocterms) \
.filter(
db.or_(
ft_vocterm.term.in_(featuretypes),
ft_vocterm.ancestor_vocterms.any(VocTerm.term.in_(featuretypes))
)
) \
.filter(sub_marker._marker_key==Marker._marker_key) \
.correlate(Marker)
query = query.filter(
sq.exists()
)
if nomen:
nomen = nomen.lower()
# query Marker symbol, name, synonyms
# query = query.filter(
# db.or_(db.func.lower(Marker.symbol).like(nomen),
# db.func.lower(Marker.name).like(nomen),
# Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen))
# )
# )
query1 = query.filter(db.func.lower(Marker.symbol).like(nomen))
query2 = query.filter(db.func.lower(Marker.name).like(nomen))
query3 = query.filter(Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen)))
query = query1.union(query2).union(query3)
query = query.order_by(Marker.markerstatus, Marker.symbol)
if limit:
query = query.limit(limit)
markers = query.all()
# batch load some related data needed on summary page
batchLoadAttribute(markers, 'synonyms')
#batchLoadAttribute(markers, 'secondary_mgiids')
batchLoadAttribute(markers, 'featuretype_vocterms')
return markers
def searchMarkers(nomen=None, refs_id=None, featuretypes=None, limit=None):
"""
Perform search for Marker records by various parameters
e.g. nomen, _refs_key
ordered by Marker.symbol
"""
query = Marker.query.filter_by(_organism_key=1)
if refs_id:
reference_accession = db.aliased(Accession)
sub_marker = db.aliased(Marker)
sq = db.session.query(sub_marker) \
.join(sub_marker.all_references) \
.join(reference_accession, Reference.jnumid_object) \
.filter(reference_accession.accid==refs_id) \
.filter(sub_marker._marker_key==Marker._marker_key) \
.correlate(Marker)
query = query.filter(sq.exists())
if featuretypes:
ft_vocterm = db.aliased(VocTerm)
sub_marker = db.aliased(Marker)
sq = db.session.query(sub_marker) \
.join(ft_vocterm, sub_marker.featuretype_vocterms) \
.filter(
db.or_(
ft_vocterm.term.in_(featuretypes),
ft_vocterm.ancestor_vocterms.any(VocTerm.term.in_(featuretypes))
)
) \
.filter(sub_marker._marker_key==Marker._marker_key) \
.correlate(Marker)
query = query.filter(sq.exists())
if nomen:
nomen = nomen.lower()
# query Marker symbol, name, synonyms
# query = query.filter(
# db.or_(db.func.lower(Marker.symbol).like(nomen),
# db.func.lower(Marker.name).like(nomen),
# Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen))
# )
# )
query1 = query.filter(db.func.lower(Marker.symbol).like(nomen))
query2 = query.filter(db.func.lower(Marker.name).like(nomen))
query3 = query.filter(
Marker.synonyms.any(db.func.lower(Synonym.synonym).like(nomen)))
query = query1.union(query2).union(query3)
query = query.order_by(Marker.markerstatus, Marker.symbol)
if limit:
query = query.limit(limit)
markers = query.all()
# batch load some related data needed on summary page
batchLoadAttribute(markers, 'synonyms')
#batchLoadAttribute(markers, 'secondary_mgiids')
batchLoadAttribute(markers, 'featuretype_vocterms')
return markers
def searchProbes(marker_id=None,
refs_id=None,
probe_name=None,
segmenttypes=None,
limit=None):
"""
Perform search for Probe records by various parameters
e.g. marker_id, _refs_id
ordered by Probe.name
"""
query = Probe.query
if segmenttypes:
segtypeAlias = db.aliased(VocTerm)
query = query.join(segtypeAlias, Probe.segmenttype_obj) \
.filter(segtypeAlias.term.in_(segmenttypes))
if refs_id:
ref_accession = db.aliased(Accession)
sub_probe = db.aliased(Probe)
sq = db.session.query(sub_probe) \
.join(sub_probe.references) \
.join(ref_accession, Reference.jnumid_object) \
.filter(ref_accession.accid==refs_id) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query = query.filter(
sq.exists()
)
if marker_id:
marker_accession = db.aliased(Accession)
sub_probe = db.aliased(Probe)
sq = db.session.query(sub_probe) \
.join(sub_probe.markers) \
.join(marker_accession, Marker.mgiid_object) \
.filter(marker_accession.accid==marker_id) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query = query.filter(
sq.exists()
)
if probe_name:
probe_name = probe_name.lower()
probeAlias = db.aliased(ProbeAlias)
probeRef = db.aliased(ProbeReferenceCache)
sub_probe = db.aliased(Probe)
alias_sq = db.session.query(sub_probe) \
.join(probeRef, sub_probe._probe_reference_caches) \
.join(probeAlias, probeRef.probe_aliases) \
.filter(db.func.lower(probeAlias.alias).like(probe_name)) \
.filter(sub_probe._probe_key==Probe._probe_key) \
.correlate(Probe)
query1 = query.filter(db.func.lower(Probe.name).like(probe_name))
query2 = query.filter(alias_sq.exists())
query = query1.union(query2)
query = query.order_by(Probe.name)
if limit:
query = query.limit(limit)
probes = query.all()
# batch load some related data needed on summary page
batchLoadAttribute(probes, 'source')
batchLoadAttribute(probes, 'markers')
batchLoadAttribute(probes, 'references')
batchLoadAttribute(probes, '_probe_marker_caches')
batchLoadAttribute(probes, '_probe_reference_caches')
batchLoadAttribute(probes, '_probe_reference_caches.probe_aliases')
batchLoadAttribute(probes, 'derivedfrom_probe')
# probe_assocs = []
# for probe in probes:
# probe_assocs.extend(probe.probe_marker_caches)
# batchLoadAttribute(probe_assocs, 'marker')
return probes
