def get_by_jnum_id(self, jnum_id):
"""
Return a reference by jnum_id
"""
jnum_id = jnum_id.upper()
sub_ref = db.aliased(Reference)
accession_model = db.aliased(Accession)
sq = db.session.query(sub_ref)
sq = sq.join(accession_model, sub_ref.jnumid_object)
sq = sq.filter(accession_model.accid==jnum_id)
sq = sq.filter(sub_ref._refs_key==Reference._refs_key)
sq = sq.correlate(Reference)
query = Reference.query
return query.filter( sq.exists() ).first()
def get_by_primary_id(self, id):
"""
Returns a VocTerm object using its primary ID
(Not necessarily an MGI ID, could be MP: or GO:, etc
"""
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 _build_search_query(self, search_query):
"""
Search Result by fields:
marker_id
refs_id
direct_structure_id
"""
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 search_query.has_valid_param("marker_id"):
marker_id = search_query.get_value("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()
)
has_refs_id_query = search_query.has_valid_param("refs_id")
if has_refs_id_query:
search_query.get_value("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 search_query.has_valid_param("direct_structure_id"):
direct_structure_id = search_query.get_value("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 has_refs_id_query:
# 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 _build_search_query(self, search_query):
"""
Search GXDIndexRecord by fields:
_refs_key
_marker_key
_priority_key
_conditionalmutants_key
comments
_modifiedby_key
_modifiedby_key
"""
query = GxdIndexRecord.query
# join to Marker for sorting
query = query.join(GxdIndexRecord.marker)
# join to Reference for sorting
# also for query
query = query.join(GxdIndexRecord.reference)
hasRefsKeySearch = search_query.has_valid_param('_refs_key')
if hasRefsKeySearch:
_refs_key = search_query.get_value('_refs_key')
query = query.filter(GxdIndexRecord._refs_key==_refs_key)
if not hasRefsKeySearch:
# reference-related searches only need to occur if we did not
# also search a specific _Refs_key
if search_query.has_valid_param('short_citation'):
short_citation = search_query.get_value('short_citation')
short_citation = short_citation.lower()
query = query.join(Reference.citation_cache)
query = query.filter(
db.func.lower(ReferenceCitationCache.short_citation).like(short_citation)
)
hasMarkerKeySearch = search_query.has_valid_param('_marker_key')
if hasMarkerKeySearch:
_marker_key = search_query.get_value('_marker_key')
query = query.filter(GxdIndexRecord._marker_key==_marker_key)
if not hasMarkerKeySearch:
# marker-related searches only needs to occur if we did not
# also search a specific _marker_key
if search_query.has_valid_param('marker_symbol'):
marker_symbol = search_query.get_value('marker_symbol')
marker_symbol = marker_symbol.lower()
query = query.filter(
db.func.lower(Marker.symbol).like(marker_symbol)
)
if search_query.has_valid_param('_priority_key'):
_priority_key = search_query.get_value('_priority_key')
query = query.filter(GxdIndexRecord._priority_key==_priority_key)
if search_query.has_valid_param('comments'):
comments = search_query.get_value('comments')
comments = comments.lower()
query = query.filter(db.func.lower(GxdIndexRecord.comments).like(comments))
if search_query.has_valid_param('is_coded'):
is_coded = search_query.get_value('is_coded')
query = query.filter(GxdIndexRecord.fully_coded==is_coded)
if search_query.has_valid_param('_conditionalmutants_key'):
_conditionalmutants_key = search_query.get_value('_conditionalmutants_key')
query = query.filter(GxdIndexRecord._conditionalmutants_key==_conditionalmutants_key)
if search_query.has_valid_param('_createdby_key'):
_createdby_key = search_query.get_value('_createdby_key')
query = query.filter(GxdIndexRecord._createdby_key==_createdby_key)
if search_query.has_valid_param('createdby_login'):
login = search_query.get_value('createdby_login')
login = login.lower()
createdby_alias = db.aliased(MGIUser)
query = query.join(createdby_alias, GxdIndexRecord.createdby)
query = query.filter(db.func.lower(createdby_alias.login).like(login))
if search_query.has_valid_param('_modifiedby_key'):
_modifiedby_key = search_query.get_value('_modifiedby_key')
query = query.filter(GxdIndexRecord._modifiedby_key==_modifiedby_key)
if search_query.has_valid_param('modifiedby_login'):
login = search_query.get_value('modifiedby_login')
login = login.lower()
modifiedby_alias = db.aliased(MGIUser)
query = query.join(modifiedby_alias, GxdIndexRecord.modifiedby)
query = query.filter(db.func.lower(modifiedby_alias.login).like(login))
if search_query.has_valid_param('creation_date'):
creation_date = search_query.get_value('creation_date')
query = DateHelper().build_query_with_date(query, GxdIndexRecord.creation_date, creation_date)
if search_query.has_valid_param('modification_date'):
modification_date = search_query.get_value('modification_date')
query = DateHelper().build_query_with_date(query, GxdIndexRecord.modification_date, modification_date)
if search_query.has_valid_param('indexstages'):
indexstages = search_query.get_value('indexstages')
# AND the stages together
logger.debug("Querying indexstages = %s" % indexstages)
for indexstage in indexstages:
sub_indexrecord = db.aliased(GxdIndexRecord)
sub_indexstage = db.aliased(GxdIndexStage)
sq = db.session.query(sub_indexrecord)
sq = sq.join(sub_indexstage, sub_indexrecord.indexstages)
sq = sq.filter(sub_indexstage._stageid_key==indexstage['_stageid_key'])
sq = sq.filter(sub_indexstage._indexassay_key==indexstage['_indexassay_key'])
sq = sq.filter(sub_indexrecord._index_key==GxdIndexRecord._index_key)
sq.correlate(GxdIndexRecord)
query = query.filter(sq.exists())
# OR the stages together
# sqs = []
# for indexstage in indexstages:
# sub_indexrecord = db.aliased(GxdIndexRecord)
# sub_indexstage = db.aliased(GxdIndexStage)
# sq = db.session.query(sub_indexrecord)
# sq = sq.join(sub_indexstage, sub_indexrecord.indexstages)
# sq = sq.filter(sub_indexstage._stageid_key==indexstage['_stageid_key'])
# sq = sq.filter(sub_indexstage._indexassay_key==indexstage['_indexassay_key'])
# sq = sq.filter(sub_indexrecord._index_key==GxdIndexRecord._index_key)
# sq.correlate(GxdIndexRecord)
#
# sqs.append(sq.exists())
#
# query = query.filter(db.or_(*sqs))
# eager-load the marker and reference relationships
query = query.options(db.subqueryload('marker'))
query = query.options(db.subqueryload('reference'))
query = query.order_by(Marker.symbol, Reference.authors)
return query
def search(self, accids=None,
journal=None,
title=None,
authors=None,
primeAuthor=None,
volume=None,
year=None,
marker_id=None,
allele_id=None,
limit=None):
"""
Search references by
accids,
journal,
title,
authors,
primAuthor,
volume,
year,
marker_id,
allele_id
"""
query = Reference.query
if authors:
authors = authors.lower()
query = query.filter(
db.func.lower(Reference.authors).like(authors),
)
if primeAuthor:
primeAuthor = primeAuthor.lower()
query = query.filter(
db.func.lower(Reference._primary).like(primeAuthor),
)
if journal:
journal = journal.lower()
query = query.filter(
db.func.lower(Reference.journal).like(journal),
)
if title:
title = title.lower()
query = query.filter(
db.func.lower(Reference.title).like(title),
)
if volume:
volume = volume.lower()
query = query.filter(db.func.lower(Reference.vol)==volume)
if year:
query = query.filter(Reference.year==int(year))
if marker_id:
marker_accession = db.aliased(Accession)
sub_reference = db.aliased(Reference)
sq = db.session.query(sub_reference) \
.join(sub_reference.all_markers) \
.join(marker_accession, Marker.mgiid_object) \
.filter(marker_accession.accid==marker_id) \
.filter(sub_reference._refs_key==Reference._refs_key) \
.correlate(Reference)
query = query.filter(
sq.exists()
)
if allele_id:
allele_accession = db.aliased(Accession)
sub_reference = db.aliased(Reference)
sq = db.session.query(sub_reference) \
.join(sub_reference.explicit_alleles) \
.join(allele_accession, Allele.mgiid_object) \
.filter(allele_accession.accid==allele_id) \
.filter(sub_reference._refs_key==Reference._refs_key) \
.correlate(Reference)
query = query.filter(
sq.exists()
)
if accids:
# split and prep the IDs
accids = accids.lower()
accidsToSearch = splitCommaInput(accids)
jnum_accession = db.aliased(Accession)
sub_ref1 = db.aliased(Reference)
ref_sq = db.session.query(sub_ref1) \
.join(jnum_accession, sub_ref1.jnumid_object) \
.filter(db.func.lower(jnum_accession.accid).in_(accidsToSearch)) \
.filter(sub_ref1._refs_key==Reference._refs_key) \
.correlate(Reference)
pmed_accession = db.aliased(Accession)
sub_ref2 = db.aliased(Reference)
pmed_sq = db.session.query(sub_ref2) \
.join(pmed_accession, sub_ref2.pubmedid_object) \
.filter(db.func.lower(pmed_accession.accid).in_(accidsToSearch)) \
.filter(sub_ref2._refs_key==Reference._refs_key) \
.correlate(Reference)
query1 = query.filter(ref_sq.exists())
query2 = query.filter(pmed_sq.exists())
query = query1.union(query2)
# setting sort
query = query.order_by(Reference._refs_key.desc())
# setting limit on number of returned references
if limit:
query = query.limit(limit)
references = query.all()
return references
def _build_search_query(self, search_query):
query = VocTerm.query
if search_query.has_valid_param("isobsolete"):
isobsolete = search_query.get_value("isobsolete")
query = query.filter(VocTerm.isobsolete==isobsolete)
if search_query.has_valid_param("_vocab_key"):
query = query.filter(VocTerm._vocab_key==search_query.get_value("_vocab_key"))
if search_query.has_valid_param("_term_key"):
query = query.filter(VocTerm._term_key==search_query.get_value("_term_key"))
if search_query.has_valid_param("term"):
term = search_query.get_value("term")
term = term.lower()
query = query.filter(db.func.lower(VocTerm.term)==term)
if search_query.has_valid_param("vocab_name"):
vocab_name = search_query.get_value("vocab_name")
vocab_name = vocab_name.lower()
vocab_alias = db.aliased(Vocab)
query = query.join(vocab_alias, VocTerm.vocab)
query = query.filter(db.func.lower(vocab_alias.name)==vocab_name)
# Specific to EMAPA Browser stage searching
if search_query.has_valid_param('stageSearch'):
stageSearch = search_query.get_value('stageSearch')
stages = emapaStageParser(stageSearch)
if stages:
stages = [int(stage) for stage in stages]
emapa_alias = db.aliased(VocTermEMAPA)
emaps_alias = db.aliased(VocTermEMAPS)
sub_term = db.aliased(VocTerm)
sq = db.session.query(sub_term) \
.join(emapa_alias, sub_term.emapa_info) \
.join(emaps_alias, emapa_alias.emaps_infos) \
.filter(emaps_alias._stage_key.in_(stages)) \
.filter(sub_term._term_key==VocTerm._term_key) \
.correlate(VocTerm)
query = query.filter(sq.exists())
# Specific to EMAPA Browser term searching
if search_query.has_valid_param('termSearch'):
termSearch = search_query.get_value('termSearch')
termSearch = termSearch.lower()
termsToSearch = splitSemicolonInput(termSearch)
# query IDs, terms, and synonyms then UNION all
# search accession ID
accession_alias = db.aliased(Accession)
sub_term1 = db.aliased(VocTerm)
id_sq = db.session.query(sub_term1) \
.join(accession_alias, sub_term1.all_accession_ids) \
.filter(db.func.lower(accession_alias.accid).in_(termsToSearch)) \
.filter(sub_term1._term_key==VocTerm._term_key) \
.correlate(VocTerm)
# search terms
sub_term2 = db.aliased(VocTerm)
term_sq = db.session.query(sub_term2) \
.filter(db.or_(
db.func.lower(VocTerm.term).like(term) for term in termsToSearch \
)) \
.filter(sub_term2._term_key==VocTerm._term_key) \
.correlate(VocTerm)
# search synonyms
synonym_alias = db.aliased(Synonym)
sub_term3 = db.aliased(VocTerm)
synonym_sq = db.session.query(sub_term3) \
.join(synonym_alias, sub_term3.synonyms) \
.filter(db.or_(
db.func.lower(synonym_alias.synonym).like(term) for term in termsToSearch \
)) \
.filter(sub_term3._term_key==VocTerm._term_key) \
.correlate(VocTerm)
# perform union
query1 = query.filter(id_sq.exists())
query2 = query.filter(term_sq.exists())
query3 = query.filter(synonym_sq.exists())
query = query1.union(query2).union(query3)
# handle sorts
sorts = []
if len(search_query.sorts) > 0:
for sort_name in search_query.sorts:
if sort_name == "sequencenum":
sorts.append(VocTerm.sequencenum.asc())
elif sort_name == "term":
sorts.append(VocTerm.term.asc())
else:
sorts.append(VocTerm.sequencenum.asc())
query = query.order_by(*sorts)
return query