def generate_query_for_summative_or_interim(connection, asmt_type, student_ids, asmt_year, date_taken): fact_table = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW) dim_asmt = connection.get_table(Constants.DIM_ASMT) query = Select( [ distinct(fact_table.c.student_id).label(Constants.STUDENT_ID), fact_table.c.state_code.label(Constants.STATE_CODE), dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR), fact_table.c.date_taken.label(Constants.DATETAKEN), fact_table.c.district_id.label(Constants.DISTRICT_ID), fact_table.c.school_id.label(Constants.SCHOOL_ID), fact_table.c.asmt_grade.label(Constants.ASMT_GRADE) ], from_obj=[ fact_table.join( dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id, dim_asmt.c.rec_status == Constants.CURRENT, dim_asmt.c.asmt_type == asmt_type, dim_asmt.c.asmt_period_year == asmt_year)) ]) query = query.where( and_(fact_table.c.rec_status == Constants.CURRENT, fact_table.c.student_id.in_(student_ids))) query = query.order_by(fact_table.c.student_id, fact_table.c.date_taken) if date_taken is not None: query = query.where(and_(fact_table.c.date_taken == date_taken)) return query
def apply_ordering( cls, statement: Select, ordering: Optional["OrderingQuery"], ) -> Select: if ordering is None or not ordering.orderings: return statement order_by_clause = [] for x in ordering.orderings: asc: Optional[bool] = None if x.startswith("-"): asc = False field = x[1:] elif x.startswith("+"): asc = True field = x[1:] else: asc = None field = x if field.startswith("_"): continue sa_column = getattr(cls, field, None) if sa_column is not None and isinstance(sa_column, InstrumentedAttribute): if asc is None: order_by_clause.append(sa_column) elif asc: order_by_clause.append(sa_column.asc()) else: order_by_clause.append(sa_column.desc()) if len(order_by_clause) > 0: statement = statement.order_by(*order_by_clause) return statement
def generate_query_for_summative_or_interim(connection, asmt_type, student_ids, asmt_year, date_taken): fact_table = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW) dim_asmt = connection.get_table(Constants.DIM_ASMT) query = Select([distinct(fact_table.c.student_id).label(Constants.STUDENT_ID), fact_table.c.state_code.label(Constants.STATE_CODE), dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR), fact_table.c.date_taken.label(Constants.DATETAKEN), fact_table.c.district_id.label(Constants.DISTRICT_ID), fact_table.c.school_id.label(Constants.SCHOOL_ID), fact_table.c.asmt_grade.label(Constants.ASMT_GRADE)], from_obj=[fact_table .join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id, dim_asmt.c.rec_status == Constants.CURRENT, dim_asmt.c.asmt_type == asmt_type, dim_asmt.c.asmt_period_year == asmt_year))]) query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT, fact_table.c.student_id.in_(student_ids))) query = query.order_by(fact_table.c.student_id, fact_table.c.date_taken) if date_taken is not None: query = query.where(and_(fact_table.c.date_taken == date_taken)) return query