def _combined(session, dataset):
"""
args:
session (Session): SQLAlchemy session
runcat (Runningcatalog): Running catalog model object
dataset (Dataset): Dataset model object
return: a SQLALchemy subquery
"""
runcat = aliased(Runningcatalog, name='r')
match_assoc = aliased(Assocxtrsource, name='match_assoc')
match_ex = aliased(Extractedsource, name='match_ex')
match_img = aliased(Image, name='match_img')
agg_img = aliased(Image, name='agg_img')
agg_assoc = aliased(Assocxtrsource, name='agg_assoc')
agg_ex = aliased(Extractedsource, name='agg_ex')
newsrc_trigger_query = _newsrc_trigger(session, dataset)
last_ts_fmax_query = _last_ts_fmax(session, dataset)
return session.query(
runcat.id.label('runcat'),
runcat.wm_ra.label('ra'),
runcat.wm_decl.label('decl'),
runcat.wm_uncertainty_ew,
runcat.wm_uncertainty_ns,
runcat.xtrsrc_id,
runcat.dataset_id.label('dataset_id'),
runcat.datapoints,
match_assoc.v_int,
match_assoc.eta_int,
match_img.band_id,
newsrc_trigger_query.c.id.label('newsource'),
newsrc_trigger_query.c.sigma_rms_max.label('sigma_rms_max'),
newsrc_trigger_query.c.sigma_rms_min.label('sigma_rms_min'),
func.max(agg_ex.f_int).label('lightcurve_max'),
func.avg(agg_ex.f_int).label('lightcurve_avg'),
func.median(agg_ex.f_int).label('lightcurve_median')
). \
select_from(last_ts_fmax_query). \
join(match_assoc, match_assoc.runcat_id == last_ts_fmax_query.c.runcat_id). \
join(match_ex,
(match_assoc.xtrsrc_id == match_ex.id) &
(match_ex.f_int == last_ts_fmax_query.c.max_flux)). \
join(runcat, runcat.id == last_ts_fmax_query.c.runcat_id). \
join(match_img, match_ex.image_id == match_img.id). \
outerjoin(newsrc_trigger_query, newsrc_trigger_query.c.rc_id == runcat.id). \
join(agg_assoc, runcat.id == agg_assoc.runcat_id). \
join(agg_ex, agg_assoc.xtrsrc_id == agg_ex.id). \
join(agg_img,
(agg_ex.image_id == agg_img.id) & (agg_img.band_id == match_img.band_id)). \
group_by(runcat.id,
runcat.wm_ra,
runcat.wm_decl,
runcat.wm_uncertainty_ew,
runcat.wm_uncertainty_ns,
runcat.xtrsrc_id,
runcat.dataset_id,
runcat.datapoints,
match_assoc.v_int,
match_assoc.eta_int,
match_img.band_id,
newsrc_trigger_query.c.id,
newsrc_trigger_query.c.sigma_rms_max,
newsrc_trigger_query.c.sigma_rms_min,
). \
filter(runcat.dataset == dataset). \
subquery()
def build_query_to_report(self, query, aggregate_table):
median = func.median(aggregate_table.c.rtt_samples)
return query.column(label("MedianRTT", median))
def build_query_to_report(self, query, aggregate_table):
median = func.median(aggregate_table.c.upload_samples)
return query.column(label("upload_median", median))
def _combined(session, dataset):
"""
args:
session (Session): SQLAlchemy session
runcat (Runningcatalog): Running catalog model object
dataset (Dataset): Dataset model object
return: a SQLALchemy subquery
"""
runcat = aliased(Runningcatalog, name='r')
match_assoc = aliased(Assocxtrsource, name='match_assoc')
match_ex = aliased(Extractedsource, name='match_ex')
match_img = aliased(Image, name='match_img')
agg_img = aliased(Image, name='agg_img')
agg_assoc = aliased(Assocxtrsource, name='agg_assoc')
agg_ex = aliased(Extractedsource, name='agg_ex')
newsrc_trigger_query = _newsrc_trigger(session, dataset)
last_ts_fmax_query = _last_ts_fmax(session, dataset)
return session.query(
runcat.id.label('runcat'),
runcat.wm_ra.label('ra'),
runcat.wm_decl.label('decl'),
runcat.wm_uncertainty_ew,
runcat.wm_uncertainty_ns,
runcat.xtrsrc_id,
runcat.dataset_id.label('dataset_id'),
runcat.datapoints,
match_assoc.v_int,
match_assoc.eta_int,
match_img.band_id,
newsrc_trigger_query.c.id.label('newsource'),
newsrc_trigger_query.c.sigma_rms_max.label('sigma_rms_max'),
newsrc_trigger_query.c.sigma_rms_min.label('sigma_rms_min'),
func.max(agg_ex.f_int).label('lightcurve_max'),
func.avg(agg_ex.f_int).label('lightcurve_avg'),
func.median(agg_ex.f_int).label('lightcurve_median')
). \
select_from(last_ts_fmax_query). \
join(match_assoc, match_assoc.runcat_id == last_ts_fmax_query.c.runcat_id). \
join(match_ex,
(match_assoc.xtrsrc_id == match_ex.id) &
(match_ex.f_int == last_ts_fmax_query.c.max_flux)). \
join(runcat, runcat.id == last_ts_fmax_query.c.runcat_id). \
join(match_img, match_ex.image_id == match_img.id). \
outerjoin(newsrc_trigger_query, newsrc_trigger_query.c.rc_id == runcat.id). \
join(agg_assoc, runcat.id == agg_assoc.runcat_id). \
join(agg_ex, agg_assoc.xtrsrc_id == agg_ex.id). \
join(agg_img,
(agg_ex.image_id == agg_img.id) & (agg_img.band_id == match_img.band_id)). \
group_by(runcat.id,
runcat.wm_ra,
runcat.wm_decl,
runcat.wm_uncertainty_ew,
runcat.wm_uncertainty_ns,
runcat.xtrsrc_id,
runcat.dataset_id,
runcat.datapoints,
match_assoc.v_int,
match_assoc.eta_int,
match_img.band_id,
newsrc_trigger_query.c.id,
newsrc_trigger_query.c.sigma_rms_max,
newsrc_trigger_query.c.sigma_rms_min,
). \
filter(runcat.dataset == dataset). \
subquery()