def room_assistance_emails(): inner = (Request.query.filter( Request.type == 'room-assistance', Request.state == RequestState.accepted).add_columns( func.jsonb_array_elements_text( Request.data['occurrences']).label('requested_at')).subquery()) aliased_event = aliased(Event, name='event') requests = (db.session.query(inner, aliased_event).join( aliased_event, aliased_event.id == inner.c.event_id).filter( aliased_event.own_room_id.isnot(None)).filter( db.cast(inner.c.requested_at, db.Date) == db.cast( now_utc(), db.Date)).all()) requests = [req._asdict() for req in requests] template = get_plugin_template_module('emails/room_assistance_emails.html', requests=requests, parse_dt=dateutil.parser.parse) recipients = RoomAssistancePlugin.settings.get( 'room_assistance_recipients') if recipients: email = make_email(from_address=config.NO_REPLY_EMAIL, to_list=recipients, template=template, html=True) send_email(email)
def _find_requests(from_dt=None, to_dt=None): inner = (Request.query .filter(Request.type == 'room-assistance', Request.state == RequestState.accepted) .add_columns(func.jsonb_array_elements_text(Request.data['occurrences']).label('requested_at')) .subquery()) aliased_event = aliased(Event, name='event') query = db.session.query(inner, aliased_event).join(aliased_event, aliased_event.id == inner.c.event_id) if from_dt: query = query.filter(db.cast(inner.c.requested_at, db.DateTime) >= from_dt) if to_dt: query = query.filter(db.cast(inner.c.requested_at, db.DateTime) <= to_dt) return [req._asdict() for req in query]
def test_aggregate_scalar_over_table_valued(self, metadata, connection): test = Table("test", metadata, Column("id", Integer), Column("data", JSONB)) test.create(connection) connection.execute( test.insert(), [ { "id": 1, "data": { "key": [23.7, 108.17, 55.98] } }, { "id": 2, "data": { "key": [2.320, 9.55] } }, { "id": 3, "data": { "key": [10.5, 6] } }, ], ) elem = (func.jsonb_array_elements_text( test.c.data["key"]).table_valued("value").alias("elem")) maxdepth = select(func.max(cast(elem.c.value, Float))).label("maxdepth") stmt = select(test.c.id.label("test_id"), maxdepth).order_by("maxdepth") eq_( connection.execute(stmt).all(), [(2, 9.55), (3, 10.5), (1, 108.17)])