def post(self, request, **kwargs): """This method will return users results in JSON format :param request: :return JSON response """ user_to_search = request.POST.get('term', False) query = User.query.filter( User.customerId == request.user.customer_id, User.isActive == 1, functions.concat(User.firstName, ' ', User.LastName).startswith(user_to_search)) order_by_clause = functions.concat(User.firstName, ' ', User.LastName) query = query.order_by(order_by_clause).with_entities( User.userId, User.firstName, User.LastName).limit(10) users = query.all() result = [] for user in users: result.append({ 'id': user.userId, 'name': user.firstName + ' ' + user.LastName, 'avatar': '', 'type': 'user' }) return JsonResponse(dict(users=result))
def retrieve_flights(): airport_origin = aliased(Airport) airport_destination = aliased(Airport) flights = db.session.query(Flight.id,functions.concat(airport_origin.city,", ",airport_origin.country," (",airport_origin.airport_code,")").label("origin") \ ,functions.concat(airport_destination.city,", ",airport_destination.country," (",airport_destination.airport_code,")").label("destination") \ ,Flight.duration).filter(Flight.origin_id==airport_origin.id).filter(Flight.destination_id==airport_destination.id).all() return flights
def find_nice_twins(self, max_check=1000): session = mv_model.Session() open_twins = list( session.query(mv_model.TwinDeck).filter( mv_model.TwinDeck.standard_key == None)) random.shuffle(open_twins) print(f"Analyzing {len(open_twins)} twin decks") for potential_twin in open_twins[:max_check]: potential_twin_deck = potential_twin.evil_deck house_decks = session.query( mv_model.Deck).filter(mv_model.Deck.expansion == 496) house_decks = house_decks.filter( concat(potential_twin_deck.name).like( concat('%', mv_model.Deck.name, '%'))) for d in house_decks: if d.name == potential_twin_deck.name: continue print("found!", potential_twin_deck.name, '-', d.name) pt_card_names = sorted( [c.name for c in potential_twin_deck.cards]) t_card_names = sorted([c.name for c in d.cards]) if pt_card_names == t_card_names: print(" - matching cards") potential_twin.standard_key = d.key session.add(potential_twin) session.commit() #self.alert(f'Found twin: {potential_twin.standard_key}, {potential_twin.evil_key}', ['discord']) break time.sleep(0.5)
def get(self, request, **kwargs): """This method will return users results in JSON format :param request: :return JSON response """ user_to_search = request.GET.get('term', '') page = int(request.GET.get('page', 1)) page_limit = int(request.GET.get('page_limit', 25)) query = User.query.filter( User.customerId == request.user.customer_id, User.isActive == 1, functions.concat(User.firstName, ' ', User.LastName).startswith(user_to_search)) order_by_clause = functions.concat(User.firstName, ' ', User.LastName) total_users = query.with_entities(func.count('*')).scalar() offset = (page - 1) * page_limit query = query.order_by(order_by_clause).with_entities( User.userId, User.firstName, User.LastName).offset(offset).limit(page_limit) users = query.all() users = [{ 'id': user_find.userId, 'text': user_find.firstName + ' ' + user_find.LastName } for user_find in users] return JsonResponse(dict(users=users, total=total_users))
def flight(flight_id): airport_origin = aliased(Airport) airport_destination = aliased(Airport) flight_info = Flight.query.get(flight_id) flight = db.session.query(Flight.id ,functions.concat(airport_origin.city," (",airport_origin.airport_code,")").label('origin') ,functions.concat(airport_destination.city," (",airport_destination.airport_code,")").label('destination') ,functions.concat(Flight.duration, " minutes").label('duration'))\ .filter(Flight.origin_id==airport_origin.id).filter(Flight.destination_id==airport_destination.id).filter(Flight.id==flight_id).all() return render_template("flight.html", flight=flight[0], passengers=flight_info.passengers)
def records_from_full_name(ukrdc3: Session, names: Iterable[str]): """Finds Ids from full name""" conditions = [] for name in names: query_term: str = _convert_query_to_pg_like(name).upper() conditions.append(concat(Name.given, " ", Name.family).like(query_term)) conditions.append(concat(Name.family, " ", Name.given).like(query_term)) conditions.append(Name.given.like(query_term)) conditions.append(Name.family.like(query_term)) return ukrdc3.query(PatientRecord).join(Patient).join(Name).filter(or_(*conditions))
def get_team_briefs(self, current_user_id, domain): """Returns summary of live and closed briefs submitted by the user's team.""" team_ids = db.session.query(User.id).filter( User.id != current_user_id, User.email_address.endswith(concat('@', domain))) team_brief_ids = db.session.query(BriefUser.brief_id).filter( BriefUser.user_id.in_(team_ids)) results = (db.session.query( Brief.id, Brief.data['title'].astext.label('name'), Brief.closed_at, Brief.status, Framework.slug.label('framework'), Lot.slug.label('lot'), User.name.label('author'), func.count(BriefResponse.id).label('applications')).join( BriefUser, Framework, Lot, User).filter( Brief.id.in_(team_brief_ids), or_(Brief.status == 'live', Brief.status == 'closed')).outerjoin( BriefResponse, Brief.id == BriefResponse.brief_id).group_by( Brief.id, Framework.slug, Lot.slug, User.name).order_by( sql_case([(Brief.status == 'live', 1), (Brief.status == 'closed', 2)]), Brief.closed_at.desc().nullslast(), Brief.id.desc()).all()) return [r._asdict() for r in results]
def get_sequence_descriptions_from_pfam_without_join_table(pfam, with_pdb): subquery = get_pfam_acc_from_pfam(pfam) subquery = subquery.distinct().subquery() query = db.session.query( concat(Pfamseq.pfamseq_id, '/', cast(PfamARegFullSignificant.seq_start, types.Unicode), '-', cast(PfamARegFullSignificant.seq_end, types.Unicode))) query = query.join( PfamARegFullSignificant, Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc) query = query.filter( PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc) if with_pdb: subquery2 = db.session.query(PdbPfamAReg) subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c. pfamA_acc).distinct().subquery() query = query.filter( PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc) query = query.filter(PfamARegFullSignificant.in_full) query = query.options( Load(Pfamseq).load_only('pfamseq_id'), Load(PfamARegFullSignificant).load_only("seq_start", "seq_end")) query = query.order_by(Pfamseq.pfamseq_id.asc()).distinct() results = query.all() return [r[0] for r in results]
def get_descriptions(self, code, with_pdb): #icode = "%{:}%".format(code) subquery = scoped_db.query(PfamA.pfamA_acc) subquery = subquery.filter( or_(PfamA.pfamA_acc == code.upper(), PfamA.pfamA_id.ilike(code))).distinct().subquery() query = scoped_db.query( concat(Pfamseq.pfamseq_id, '/', cast(PfamARegFullSignificant.seq_start, types.Unicode), '-', cast(PfamARegFullSignificant.seq_end, types.Unicode))) query = query.outerjoin( PfamARegFullSignificant, and_(Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc, PfamARegFullSignificant.in_full == 1)) query = query.filter( PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc) if with_pdb: subquery2 = scoped_db.query(PdbPfamAReg) subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c. pfamA_acc).distinct().subquery() query = query.filter( PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc) query = query.options( Load(Pfamseq).load_only('pfamseq_id'), Load(PfamARegFullSignificant).load_only("seq_start", "seq_end")) query = query.order_by(Pfamseq.pfamseq_id.asc()) return query.distinct().all()
def get_latest_updates(branch: str = "Stable") -> result: """ SELECT CONCAT(devices.name, ' ', devices.region) as name, latest.* FROM devices, ( SELECT all_latest.* FROM (SELECT codename, version, branch, method, size, md5, link, changelog, date from updates WHERE updates.branch = "Stable" AND updates.type = "Full" ORDER BY updates.date DESC LIMIT 99999) as all_latest GROUP BY all_latest.codename, all_latest.method) as latest WHERE latest.codename = devices.codename AND devices.miui_code != "" AND LENGTH(devices.miui_code) = 4 ORDER BY date desc """ all_latest = session.query( Update.codename, Update.version, Update.android, Update.branch, Update.method, Update.size, Update.md5, Update.link, Update.changelog, Update.date).filter(Update.branch == branch).filter( Update.type == "Full").order_by( Update.date.desc()).limit(99999).subquery() latest = session.query(all_latest).group_by( all_latest.c.codename).group_by(all_latest.c.method).subquery() updates = session.query( Device.name, concat(Device.name, ' ', Device.region).label('fullname'), latest).filter(latest.c.codename == Device.codename).filter( Device.miui_code != "").filter( func.length(Device.miui_code) == 4).order_by( latest.c.date.desc()).all() return updates
def get_descriptions(self, code, with_pdb): subquery = scoped_db.query(PfamA) subquery = subquery.filter( or_(PfamA.pfamA_acc == code.upper(), PfamA.pfamA_id.ilike(code))).distinct().subquery() query1 = scoped_db.query(PfamARegFullSignificant.pfamseq_acc, PfamARegFullSignificant.seq_start, PfamARegFullSignificant.seq_end) query1 = query1.filter( PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc, PfamARegFullSignificant.in_full) query1 = query1.options( Load(PfamARegFullSignificant).load_only("seq_start", "seq_end")) query1 = query1.distinct().subquery() # query2 = scoped_db.query(Pfamseq.pfamseq_id) # query2 = query2.filter(Pfamseq.pfamA_acc == subquery.c.pfamA_acc).distinct().subquery() query = scoped_db.query( concat(Pfamseq.pfamseq_id, '/', cast(query1.c.seq_start, types.Unicode), '-', cast(query1.c.seq_end, types.Unicode))) query = query.filter(Pfamseq.pfamseq_acc == query1.c.pfamseq_acc) if with_pdb: subquery2 = scoped_db.query(PdbPfamAReg) subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c. pfamA_acc).distinct().subquery() query = query.filter( PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc) query = query.order_by(Pfamseq.pfamseq_id.asc()) return query.distinct().all()
def get_device_roms(codename) -> result: """ SELECT CONCAT(devices.name, ' ', devices.region) as name, all_updates.* FROM devices, ( SELECT codename, version, android, branch, method, size, md5, link, changelog, date from updates WHERE codename like 'whyred%' AND (updates.branch like "Stable%" OR updates.branch = "Weekly") AND updates.type = "Full" ORDER BY updates.date DESC LIMIT 99999) as all_updates WHERE devices.codename = all_updates.codename AND LENGTH(devices.miui_code) = 4 """ all_updates = session.query( Update.codename, Update.version, Update.android, Update.branch, Update.method, Update.size, Update.md5, Update.link, Update.changelog, Update.date).filter(Update.codename.startswith(codename)).filter( or_(Update.branch.startswith("Stable"), Update.branch == "Weekly")).filter( Update.type == "Full").order_by( Update.date.desc()).limit(99999).subquery() updates = session.query( concat(Device.name, ' ', Device.region).label('name'), all_updates).filter(Device.codename == all_updates.c.codename).filter( func.length(Device.miui_code) == 4).all() return updates
def venues(): data = [] upcoming_shows = 0 # Get unique cities first to fill the data array in the correct way city_records = Venue.query.distinct(concat(Venue.city, Venue.state)).all() for c_record in city_records: venues_data = [] # Get all the venues in each city venue_records = Venue.query.filter(Venue.city.like(c_record.city)) \ .filter(Venue.state.like(c_record.state)).all() for v_record in venue_records: # Get upcoming show upcoming_shows = 0 shows = db.session.query(showTable)\ .join(Venue, Venue.id == showTable.c.venue_id)\ .join(Artist, Artist.id == showTable.columns.artist_id)\ .filter(Venue.id == v_record.id).all() for show in shows: if str_to_datetime(show.start_time) > datetime.utcnow(): upcoming_shows += 1 # Append each venue data to the venues list venues_data.append({ 'id': v_record.id, 'name': v_record.name, 'num_upcomig_shows': upcoming_shows }) # Append each city to the data list data.append({ 'city': c_record.city, 'state': c_record.state, 'venues': venues_data }) return render_template('pages/venues.html', areas=data)
def make_case(query, caseNode): field_exprs = [] for node in caseNode.findall('field'): query, expr = make_expr(query, node) field_exprs.append(expr) expr = concat(*field_exprs) if len(field_exprs) > 1 else field_exprs[0] return query, caseNode.attrib.get('value', None), expr
def make_case(query, caseNode): field_exprs = [] for node in caseNode.findall('field'): query, expr = make_expr(query, node) field_exprs.append(expr) expr = concat( *field_exprs) if len(field_exprs) > 1 else field_exprs[0] return query, caseNode.attrib.get('value', None), expr
def getQuarterPeriodList(self, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(QuarterPeriod)\ .with_entities(QuarterPeriod.OID, functions.concat(QuarterPeriod.year, '-' ,QuarterPeriod.quarter))\ .order_by(QuarterPeriod.year.desc(), QuarterPeriod.quarter.desc()) objectResult = query.all() return objectResult
def pseudo_sprintf(self, format, expr): """Handle format attribute of fields in data object formatter definitions. expr - the expression giving the field to be formatted. format - an sprintf style format string. In Specify 6 this is given to the java.util.Formatter.format method with the value of expr as the sole argument. So, it seems the format sting should have only one substitution directive. Only going to handle '%s' and '%d' for now. """ if '%s' in format: before, after = format.split('%s') return concat(before, expr, after) elif '%d' in format: before, after = format.split('%d') return concat(before, expr, after) else: return format
def match_gui_query(self, cep_col, upload_col, match_desc, id_suffix): # noinspection PyCallingNonCallable session = mysql_session_maker() return session.query( self.match_table.c.ID.label("ID_1"), functions.concat(expression.cast(self.match_table.c.ID, String), literal_column("'{}'".format(id_suffix))).label("ID_2"), cep_col.label("CEP_Value"), upload_col.label("Uploaded_Value"), literal_column("'{}:'".format(match_desc), String(length=50)).label("Match_Type"))
def parse_sqla_operator(raw_key, *operands): key = raw_key.lower().strip() if not operands: raise APIError('Missing arguments for \'%s\'.' % (key)) if key in ['and']: query = and_(*operands) return query elif key in ['or']: query = or_(*operands) return query elif key in ['not']: x = operands[0] return parse_condition(x)._not() else: if len(operands) != 2: raise APIError( 'Wrong number of arguments for \'%s\'. Expected: 2 Got: %s' % (key, len(operands))) x, y = operands if key in ['equals', '=']: return x == y if key in ['greater', '>']: return x > y if key in ['lower', '<']: return x < y if key in ['notequal', '<>', '!=']: return x != y if key in ['notgreater', '<=']: return x <= y if key in ['notlower', '>=']: return x >= y if key in ['add', '+']: return x + y if key in ['substract', '-']: return x - y if key in ['multiply', '*']: return x * y if key in ['divide', '/']: return x / y if key in ['concatenate', '||']: return fun.concat(x, y) if key in ['is not']: return x.isnot(y) if key in ['<->']: return x.distance_centroid(y) if key in ['getitem']: if isinstance(y, Slice): return x[parse_single(y.start, int):parse_single(y.stop, int)] else: return x[read_pgid(y)] if key in ['in']: return x.in_(y) raise APIError("Operator '%s' not supported" % key)
def keyword_filter(query, keyword, columns): keyword = keyword.replace(' ', ' ') clauses = [] for column in columns: clause = concat(column).ilike('%{}%'.format(keyword)) clauses.append(clause) query = query.filter(or_(*clauses)) return query
def make_expr(query, fieldNode): path = fieldNode.text.split('.') query, table, model, specify_field = build_join(query, specify_model, orm_table, path, join_cache) if specify_field.is_relationship: formatter_name = fieldNode.attrib.get('formatter', None) query, expr = self.objformat(query, table, formatter_name, join_cache) else: expr = self._fieldformat(specify_field, getattr(table, specify_field.name)) if 'sep' in fieldNode.attrib: expr = concat(fieldNode.attrib['sep'], expr) return query, coalesce(expr, '')
class TreeItemViews(AbstractViews): # type: ignore """The admin tree item view.""" _list_fields = [ _list_field("id"), _list_field("name"), _list_field("description"), ] _extra_list_fields_no_parents = [ _list_field( "metadatas", renderer=lambda treeitem: ", ".join( [f"{m.name}: {m.value}" or "" for m in treeitem.metadatas]), filter_column=concat(Metadata.name, ": ", Metadata.value).label("metadata"), ) ] _extra_list_fields = [ _list_field( "parents_relation", renderer=lambda layer_wms: ", ".join([ p.treegroup.name or "" for p in sorted(layer_wms.parents_relation, key=lambda p: p.treegroup.name or "") ]), ) ] + _extra_list_fields_no_parents @view_config(route_name="c2cgeoform_item", request_method="POST", renderer="../templates/edit.jinja2") def save(self): response = super().save() # correctly handles the validation error as if there is a validation error, cstruct is empty has_to_be_registered_in_parent = (hasattr(self, "_appstruct") and self._appstruct is not None and self._appstruct.get("parent_id")) if has_to_be_registered_in_parent: parent = self._request.dbsession.query(TreeGroup).get( has_to_be_registered_in_parent) rel = LayergroupTreeitem(parent, self._obj, 100) self._request.dbsession.add(rel) return response def _base_query( # pylint: disable=arguments-differ self, query: sqlalchemy.orm.query.Query) -> sqlalchemy.orm.query.Query: return (query.outerjoin("metadatas").options( subqueryload("parents_relation").joinedload("treegroup")).options( subqueryload("metadatas")))
def find_all_with_last_appt_by_query_params_in_store(cls, store_id, **kwargs): from application.models.appointment import Appointment relationship_query = db.session.query(func.max(Appointment.id).label('last_id'), Appointment.customer_id).filter( and_(Appointment.remark != None, Appointment.remark != u'', Appointment.store_id == store_id)).group_by( Appointment.customer_id).subquery() query = db.session.query(cls, Appointment.remark).options(joinedload(cls.sales)).outerjoin( relationship_query, cls.id == relationship_query.columns.customer_id).outerjoin(Appointment, relationship_query.columns.last_id == Appointment.id).filter( and_(cls.store_id == store_id, cls.status != 'cancelled', cls.status != 'duplicated')) # hack to support paginate query.__class__ = BaseQuery if kwargs.get('intent_level'): query = query.filter(cls._intent_level == kwargs.get('intent_level')) if kwargs.get('intent_car_ids'): query = query.filter( functions.concat(',', cls.intent_car_ids).like('%,' + kwargs.get('intent_car_ids') + '%')) if kwargs.get('last_instore', None): if kwargs.get('last_instore') == 'none' or kwargs.get('last_instore') == '-1': query = query.filter(cls.last_reception_date == None) else: try: days = int(kwargs.get('last_instore')) last_instore_date = date.today() - timedelta(days=days) query = query.filter(db.func.date(cls.last_reception_date) >= last_instore_date) except: pass if kwargs.get('status'): query = query.filter(cls.status == kwargs.get('status')) if kwargs.get('keywords'): keywords = '%' + kwargs.get('keywords') + '%' query = query.filter(or_(cls.name.like(keywords), cls.mobile.like(keywords))) if kwargs.get('sales_id'): query = query.filter(cls.sales_id == int(kwargs.get('sales_id'))) page = kwargs.get('page', DEFAULT_PAGE_START) per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE) sortable_fields = ('sales_id', 'status', '_intent_level', 'last_reception_date') query_order_fixed = SortMixin.add_order_query(query, cls, sortable_fields, kwargs) return query_order_fixed.paginate(page, per_page)
def load_nodes(): session = Session() rs = session.query(NodeStatus.ip, Node.lat, Node.lon, concat(Node.country, ", ", Node.city)).join(Node, Node.ip == NodeStatus.ip).all() ip = [x[3] for x in rs] lat = [x[1] for x in rs] lng = [x[2] for x in rs] session.close() return ip, lat, lng
def get_team_members(self, current_user_id, email_domain): """Returns a list of the user's team members.""" team_ids = db.session.query(User.id).filter( User.id != current_user_id, User.email_address.endswith(concat('@', email_domain))) results = (db.session.query(User.name, User.email_address.label('email')).filter( User.id != current_user_id, User.id.in_(team_ids), User.active.is_(True)).order_by( func.lower(User.name))) return [r._asdict() for r in results]
def get_devices() -> result: """ SELECT codename, CONCAT(name, ' ', region) as name, miui_name from devices WHERE miui_code != "" AND LENGTH(miui_code) = 4 GROUP BY codename ORDER BY codename """ return session.query( Device.codename, concat(Device.name, ' ', Device.region).label('name'), Device.miui_name).filter(Device.miui_code != "").filter( func.length(Device.miui_code) == 4).order_by( Device.codename).all()
def get_all_updates() -> result: """ SELECT CONCAT(d.name, ' ', d.region) as name, firmware.codename, version, android, branch, filename, size, md5, date from firmware JOIN devices d on firmware.codename = d.codename GROUP BY md5 :return: list of firmware results """ return session.query( concat(Device.name, ' ', Device.region).label('name'), Update.codename, Update.version, Update.android, Update.branch, Update.filename, Update.size, Update.md5, Update.date).join( Device, Update.codename == Device.codename).group_by(Update.md5).all()
def make_expr(query, fieldNode): path = fieldNode.text.split('.') query, table, model, specify_field = build_join( query, specify_model, orm_table, path, join_cache) if specify_field.is_relationship: formatter_name = fieldNode.attrib.get('formatter', None) query, expr = self.objformat(query, table, formatter_name, join_cache) else: expr = self._fieldformat(specify_field, getattr(table, specify_field.name)) if 'sep' in fieldNode.attrib: expr = concat(fieldNode.attrib['sep'], expr) return query, coalesce(expr, '')
def make_expr(query, fieldNode): path = fieldNode.text.split('.') query, table, model, specify_field = query.build_join(specify_model, orm_table, path) if specify_field.is_relationship: formatter_name = fieldNode.attrib.get('formatter', None) query, expr = self.objformat(query, table, formatter_name) else: expr = self._fieldformat(specify_field, getattr(table, specify_field.name)) if 'format' in fieldNode.attrib: expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr) if 'sep' in fieldNode.attrib: expr = concat(fieldNode.attrib['sep'], expr) return query, blank_nulls(expr)
class TreeItemViews(AbstractViews): _list_fields = [ _list_field('id'), _list_field('name'), _list_field('metadata_url'), _list_field('description') ] _extra_list_fields_no_parents = [ _list_field('metadatas', renderer=lambda layers_group: ', '.join([ '{}: {}'.format(m.name, m.value) or '' for m in layers_group.metadatas ]), filter_column=concat(Metadata.name, ': ', Metadata.value).label('metadata')) ] _extra_list_fields = [ _list_field('parents_relation', renderer=lambda layer_wms: ', '.join([ p.treegroup.name or '' for p in sorted(layer_wms.parents_relation, key=lambda p: p.treegroup.name or '') ])) ] + _extra_list_fields_no_parents @view_config(route_name='c2cgeoform_item', request_method='POST', renderer='../templates/edit.jinja2') def save(self): response = super().save() # correctly handles the validation error as if there is a validation error, cstruct is empty has_to_be_registred_in_parent = (hasattr(self, '_appstruct') and self._appstruct is not None and self._appstruct.get('parent_id')) if has_to_be_registred_in_parent: parent = self._request.dbsession.query(TreeGroup).get( has_to_be_registred_in_parent) rel = LayergroupTreeitem(parent, self._obj, 100) self._request.dbsession.add(rel) return response def _base_query(self, query): return query. \ outerjoin('metadatas').\ options(subqueryload('parents_relation').joinedload('treegroup')). \ options(subqueryload('metadatas'))
def _build_response_query(self, session, p_id, org_id): query = session.query(concat('Patient/P', PatientStatus.participantId).label('subject'), HPO.name.label('awardee'), Organization.externalId.label('organization'), Site.googleGroup.label('site'), PatientStatus.patientStatus.label('patient_status'), PatientStatus.comment.label('comment'), PatientStatus.created.label('created'), PatientStatus.modified.label('modified'), PatientStatus.user.label('user'), PatientStatus.authored.label('authored')).\ filter_by(participantId=p_id, organizationId=org_id). \ join(Site, Site.siteId == PatientStatus.siteId). \ join(HPO, HPO.hpoId == Site.hpoId). \ join(Organization, Organization.organizationId == Site.organizationId) # self.print_query(query) return query
def find_last_by_location_within_1hour(self, zipcode, country_code): sub_time = datetime.utcnow() + timedelta(hours=1, minutes=0) query = self query = query.filter( functions.concat(Temperature.date, ' ', Temperature.time).__ge__('2019-12-03 22:33:00')) if (country_code): query = query.filter_by(country_code=country_code) if (zipcode): query = query.filter_by(zipcode=zipcode) return query.order_by(text('date desc')).order_by( text('time desc')).limit(1).first()
def make_expr(query, fieldNode): path = fieldNode.text.split('.') query, table, model, specify_field = query.build_join( specify_model, orm_table, path) if specify_field.is_relationship: formatter_name = fieldNode.attrib.get('formatter', None) query, expr = self.objformat(query, table, formatter_name) else: expr = self._fieldformat(specify_field, getattr(table, specify_field.name)) if 'format' in fieldNode.attrib: expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr) if 'sep' in fieldNode.attrib: expr = concat(fieldNode.attrib['sep'], expr) return query, blank_nulls(expr)
def get_team_briefs(self, current_user_id, domain): """Returns summary of live and closed briefs submitted by the user's team.""" team_ids = db.session.query(User.id).filter(User.id != current_user_id, User.email_address.endswith(concat('@', domain))) team_brief_ids = db.session.query(BriefUser.brief_id).filter(BriefUser.user_id.in_(team_ids)) results = (db.session.query(Brief.id, Brief.data['title'].astext.label('name'), Brief.closed_at, Brief.status, Framework.slug.label('framework'), Lot.slug.label('lot'), User.name.label('author'), func.count(BriefResponse.id).label('applications')) .join(BriefUser, Framework, Lot, User) .filter(Brief.id.in_(team_brief_ids), or_(Brief.status == 'live', Brief.status == 'closed')) .outerjoin(BriefResponse, Brief.id == BriefResponse.brief_id) .group_by(Brief.id, Framework.slug, Lot.slug, User.name) .order_by(sql_case([ (Brief.status == 'live', 1), (Brief.status == 'closed', 2)]), Brief.closed_at.desc().nullslast(), Brief.id.desc()) .all()) return [r._asdict() for r in results]
def build_seach_equipment_query(items_model, search_value): # Probably it is better to make filters like in Django: date_analyse__icontains="value" return db.session\ .query(items_model)\ .outerjoin(items_model.test_reason)\ .outerjoin(items_model.test_type)\ .outerjoin(items_model.test_status)\ .outerjoin(items_model.equipment)\ .outerjoin(items_model.campaign)\ .outerjoin(Campaign.created_by)\ .filter(or_( cast(TestResult.date_analyse, String).ilike("%{}%".format(search_value)), cast(TestResult.id, String).ilike("%{}%".format(search_value)), concat(cast(TestResult.id, String), func.substring(User.name, r'^([a-zA-Z]{1})'), func.substring(User.name, r'\s([a-zA-Z]){1}')) .ilike("%{}%".format(search_value)), TestReason.name.ilike("%{}%".format(search_value)), TestType.name.ilike("%{}%".format(search_value)), TestStatus.name.ilike("%{}%".format(search_value)), Equipment.serial.ilike("%{}%".format(search_value)), Equipment.equipment_number.ilike("%{}%".format(search_value)), ))\ .all()
def title_content(cls): # return literal_column('title || " " || content') return concat(cls.title, cls.content)
from c2cgeoportal_admin.views.treeitems import TreeItemViews _list_field = partial(ListField, Theme) base_schema = GeoFormSchemaNode(Theme) base_schema.add(children_schema_node(only_groups=True)) base_schema.add( colander.SequenceSchema( GeoFormManyToManySchemaNode(Functionality), name='functionalities', widget=RelationCheckBoxListWidget( select([ Functionality.id, concat(Functionality.name, '=', Functionality.value).label('label') ]).alias('functionnality_labels'), 'id', 'label', order_by='label' ), validator=manytomany_validator ) ) base_schema.add( colander.SequenceSchema( GeoFormManyToManySchemaNode(Role), name='restricted_roles', widget=RelationCheckBoxListWidget( Role,