def get_organizations(name=None): ''' Regular response option for organizations. ''' filters, querystring = get_query_params(request.args) if name: # Get one named organization. org_filter = Organization.name == raw_name(name) org = db.session.query(Organization).filter(org_filter).first() if org: return jsonify(org.asdict(True)) else: # If no org found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of organizations. query = db.session.query(Organization) # Default ordering of results ordering = desc(Organization.last_updated) for attr, value in filters.iteritems(): if 'q' in attr: query = query.filter('organization.tsv_body @@ plainto_tsquery(:search_query)').params(search_query=value) ordering = desc(func.ts_rank(Organization.tsv_body, func.plainto_tsquery(value))) else: query = query.filter(getattr(Organization, attr).ilike(format_ilike_term(value))) query = query.order_by(ordering) response = paged_results(query=query, include_args=dict(include_extras=True), page=int(request.args.get('page', 1)), per_page=int(request.args.get('per_page', 10)), querystring=querystring) return jsonify(response)
def get_organizations(name=None): ''' Regular response option for organizations. ''' filters = request.args filters, querystring = get_query_params(request.args) if name: # Get one named organization. filter = Organization.name == raw_name(name) org = db.session.query(Organization).filter(filter).first() if org: return jsonify(org.asdict(True)) else: # If no org found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of organizations. query = db.session.query(Organization) # Default ordering of results ordering = desc(Organization.last_updated) for attr, value in filters.iteritems(): if 'q' in attr: query = query.filter("organization.tsv_body @@ plainto_tsquery('%s')" % value) ordering = desc(func.ts_rank(Organization.tsv_body, func.plainto_tsquery('%s' % value))) else: query = query.filter(getattr(Organization, attr).ilike('%%%s%%' % value)) query = query.order_by(ordering) response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring) return jsonify(response)
def search(): with ScopedSession() as session: q = flask.request.args.get('query') query = session.query(Comment, User, Post, func.ts_headline('russian', Comment.text, func.plainto_tsquery('russian', q), 'HighlightAll=true').label('highlighted')).filter(Comment.user_id == User.user_id).filter(Comment.post_id == Post.post_id) query = query.filter(func.to_tsvector('russian', Comment.text).op('@@')(func.plainto_tsquery('russian', q))) comments = [] for comment, user, post, highlighted in query.order_by(func.ts_rank_cd(func.to_tsvector('russian', Comment.text), func.plainto_tsquery('russian', q)).desc(), Comment.posted.desc()).limit(100).all(): comments.append({ "id": comment.comment_id, "parent_id": comment.parent_id, "post_id": comment.post_id, "text": normalize_text(highlighted), "posted": comment.posted.strftime(DATE_FORMAT), "user_id": user.user_id, "user_name": user.name, "user_avatar": user.avatar_hash, "comment_list_id": post.comment_list_id }) resp = app.make_response(json.dumps(comments, ensure_ascii=False)) resp.mimetype = 'application/json; charset=utf-8' resp.headers['Access-Control-Allow-Origin'] = '*' return resp
def get_orgs_projects(organization_name): ''' A cleaner url for getting an organizations projects ''' # Check org name organization = Organization.query.filter_by(name=raw_name(organization_name)).first() if not organization: return "Organization not found", 404 filters, querystring = get_query_params(request.args) # Get project objects query = db.session.query(Project).filter_by(organization_name=organization.name).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' ordering = None for attr, value in filters.iteritems(): if 'q' in attr: # Returns all results if the value is empty if value: query = query.filter('project.tsv_body @@ plainto_tsquery(:search_query)').params(search_query=value) relevance_ordering_filter = func.ts_rank(Project.tsv_body, func.plainto_tsquery(value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if value == 'relevance': ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if value == 'asc': ordering_dir = 'asc' else: ordering_dir = 'desc' else: query = query.filter(getattr(Project, attr).ilike(format_ilike_term(value))) if ordering_filter_name == 'last_updated': ordering_filter = last_updated_ordering_filter elif ordering_filter_name == 'relevance' and dir(relevance_ordering_filter) != dir(None): ordering_filter = relevance_ordering_filter if ordering_dir == 'desc': ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query=query, include_args=dict(include_organization=True, include_issues=True), page=int(request.args.get('page', 1)), per_page=int(request.args.get('per_page', 10)), querystring=querystring) return jsonify(response)
def get_orgs_projects(organization_name): ''' A cleaner url for getting an organizations projects ''' # Check org name organization = Organization.query.filter_by(name=raw_name(organization_name)).first() if not organization: return "Organization not found", 404 filters, querystring = get_query_params(request.args) # Get project objects query = db.session.query(Project).filter_by(organization_name=organization.name).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' ordering = None for attr, value in filters.iteritems(): if 'q' in attr: # Returns all results if the value is empty if value: query = query.filter("project.tsv_body @@ plainto_tsquery('%s')" % value) relevance_ordering_filter = func.ts_rank(Project.tsv_body, func.plainto_tsquery('%s' % value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if(value == 'relevance'): ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if(value == 'asc'): ordering_dir = 'asc' else: ordering_dir = 'desc' else: query = query.filter(getattr(Project, attr).ilike('%%%s%%' % value)) if(ordering_filter_name == 'last_updated'): ordering_filter = last_updated_ordering_filter elif(ordering_filter_name == 'relevance' and dir(relevance_ordering_filter) != dir(None)): ordering_filter = relevance_ordering_filter if(ordering_dir == 'desc'): ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring) return jsonify(response)
async def comment_search(conn, content): result = [] query = sa.select([comments]).where( comments.c.content.match( sa.cast(func.plainto_tsquery(content), sa.TEXT))) # SELECT # comments_comments.id, # comments_comments.content # FROM # comments_comments # WHERE # comments_comments.content @@ to_tsquery( # CAST(plainto_tsquery('query text') AS TEXT) # ) async for row in conn.execute(query): result.append({ 'id': row[0], 'content': row[1], }) return result
def search() -> flask.Response: ESCAPE_CHAR = '^' comments = [] with ScopedSession() as session: q: str = flask.request.args.get('query', '').strip() try: before: Optional[float] = float( flask.request.args.get('before', '')) except ValueError: before = None user_name = flask.request.args.get('username', '') query = session.query(Comment) if len(q) > 0: if len(q) > 2 and q.startswith('"') and q.endswith('"'): q_escaped = escape_like(q[1:-1], ESCAPE_CHAR) query = query.filter( Comment.text.ilike(f'%{q_escaped}%', escape=ESCAPE_CHAR)) else: query = query.filter( Comment.text_tsv.op('@@')(func.plainto_tsquery( 'russian', q))) if len(user_name) > 0: user_id = session.query(User.user_id).filter( sql.func.lower(User.name) == user_name.lower()).scalar() query = query.filter(Comment.user_id == user_id) if before is not None: query = query.filter( Comment.posted < datetime.fromtimestamp(before)) query = query.order_by(Comment.posted.desc()).limit(SEARCH_LIMIT) for comment in query.all(): comments.append(comment.to_dict()) resp = app.make_response(json.dumps(comments, ensure_ascii=False)) return add_api_headers(resp)
def get_organizations(name=None): ''' Regular response option for organizations. ''' filters = request.args filters, querystring = get_query_params(request.args) if name: # Get one named organization. filter = Organization.name == raw_name(name) org = db.session.query(Organization).filter(filter).first() if org: # del org.tsv_body return jsonify(org.asdict(True)) else: # If no org found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of organizations. query = db.session.query(Organization) # Default ordering of results ordering = desc(Organization.last_updated) for attr, value in filters.iteritems(): if 'q' in attr: query = query.filter( "organization.tsv_body @@ plainto_tsquery('%s')" % value) ordering = desc( func.ts_rank(Organization.tsv_body, func.plainto_tsquery('%s' % value))) else: query = query.filter( getattr(Organization, attr).ilike('%%%s%%' % value)) query = query.order_by(ordering) response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring) return jsonify(response)
def search(): search_query = request.args.get('search') search_query = search_query.replace(" ", "&") search_query = search_query.replace("+", "&") shows = db.session.query( Show.id, Show.title, Show.description, func.ts_rank_cd(Show.tsv_searchable_text, func.plainto_tsquery(search_query, postgresql_regconfig='english'), 32).label('rank') ).filter( Show.tsv_searchable_text.match(search_query, postgresql_regconfig='english') ).order_by(db.text('rank DESC')).limit(10) data = [] for show in shows: data.append({ 'id': show.id, 'title': show.title, 'description': show.description, 'rank': show.rank }) return jsonify(data)
def get_organizations(name=None): ''' Regular response option for organizations. ''' filters, querystring = get_query_params(request.args) if name: # Get one named organization. org_filter = Organization.name == raw_name(name) org = db.session.query(Organization).filter(org_filter).first() if org: return jsonify(org.asdict(True)) else: # If no org found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of organizations. query = db.session.query(Organization) # Default ordering of results ordering = desc(Organization.last_updated) if 'tags[]' in filters: tags = request.args.getlist('tags[]') query = query.filter('organization.tags ?& :tags').params(tags=tags) del filters['tags[]'] for attr, value in filters.iteritems(): if 'q' in attr: query = query.filter('organization.tsv_body @@ plainto_tsquery(:search_query)').params(search_query=value) ordering = desc(func.ts_rank(Organization.tsv_body, func.plainto_tsquery(value))) else: query = query.filter(getattr(Organization, attr).ilike(format_ilike_term(value))) query = query.order_by(ordering) response = paged_results(query=query, include_args=dict(include_extras=True), page=int(request.args.get('page', 1)), per_page=int(request.args.get('per_page', 10)), querystring=querystring) return jsonify(response)
def get_projects(id=None): ''' Regular response option for projects. ''' filters, querystring = get_query_params(request.args) if id: # Get one named project. filter = Project.id == id proj = db.session.query(Project).filter(filter).first() if proj: return jsonify(proj.asdict(True)) else: # If no project found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of projects. query = db.session.query(Project).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' ordering = None for attr, value in filters.iteritems(): if 'organization' in attr: org_attr = attr.split('_')[1] query = query.join(Project.organization).filter( getattr(Organization, org_attr).ilike('%%%s%%' % value)) elif 'q' in attr: # Returns all results if the value is empty if value: query = query.filter( "project.tsv_body @@ plainto_tsquery('%s')" % value) relevance_ordering_filter = func.ts_rank( Project.tsv_body, func.plainto_tsquery('%s' % value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if (value == 'relevance'): ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if (value == 'asc'): ordering_dir = 'asc' else: ordering_dir = 'desc' else: query = query.filter( getattr(Project, attr).ilike('%%%s%%' % value)) if (ordering_filter_name == 'last_updated'): ordering_filter = last_updated_ordering_filter elif (ordering_filter_name == 'relevance' and dir(relevance_ordering_filter) != dir(None)): ordering_filter = relevance_ordering_filter if (ordering_dir == 'desc'): ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring) return jsonify(response)
def fetch_all_by_tsquery(self, tsquery, *expr, **kwargs): """ Returns all structures whose abstract matches the keywords given in the keyword string. Parameters ---------- tsquery : str A string containing the lexme that will be used for searching. The string will be automatically casted into a tsquery through the to_tsquery() function. weights : list, default=[0.1, 0.2, 0.4, 1.0] The weights specify how heavily to weigh each category of word, in the order [D-weight, C-weight, B-weight, A-weight]. normalization : int, default=32 Option that specifies whether and how a document's length should impact its rank. The integer option controls several behaviors, so it is a bit mask: you can specify one or more behaviors using | (for example, 2|4). - 0 ignores the document length - 1 divides the rank by 1 + the logarithm of the document length - 2 divides the rank by the document length - 4 divides the rank by the mean harmonic distance between extents (this is implemented only by ts_rank_cd) - 8 divides the rank by the number of unique words in document - 16 divides the rank by 1 + the logarithm of the number of unique words in document - 32 divides the rank by itself + 1 min_words : int, default=10 max_words : int, default=25 Returns ------- resultset : list List containing tuples in the form (Structure, snippet, rank). The snippet is the part of the abstract that matches the query. Ordered by rank. Notes ----- - http://www.postgresql.org/docs/current/interactive/datatype-textsearch.html Examples -------- >>> StructureAdaptor().fetch_all_by_tsquery('imatinib') [(<Structure(3FW1)>, u'[Imatinib] represents the first in a class', 0.54545500000000002), (<Structure(2GQG)>, u'[imatinib], an inhibitor of BCR-ABL. Although', 0.375), (<Structure(3CS9)>, u'[imatinib] against [imatinib]-resistant Bcr-Abl. Consistent', 0.33333299999999999), (<Structure(2HYY)>, u'[imatinib] as first-line therapy', 0.230769), (<Structure(3G0F)>, u'[imatinib] mesylate and sunitinib malate', 0.230769), (<Structure(3G0E)>, u'[imatinib] mesylate and sunitinib malate', 0.230769), (<Structure(1T46)>, u'[Imatinib] or Gleevec) demonstrates that', 0.090909100000000007)] """ weights = kwargs.get('weights', [0.1, 0.2, 0.4, 1.0]) normalization = kwargs.get('normalization', 32) min_words, max_words = kwargs.get('min_words', 10), kwargs.get('max_words', 25) if kwargs.get('plain'): tsquery = func.plainto_tsquery('english', tsquery) else: tsquery = func.to_tsquery('english', tsquery) # basic query construct query = self.query.join('XRefs').filter(and_(*expr)) query = query.join( citations, and_(citations.c.pubmed_id == cast(XRef.xref, Integer), XRef.source == 'PubMed')) # GIST index that is used for searching index = func.to_tsvector('english', citations.c.abstract).op('@@')(tsquery) query = query.filter(index).order_by("rank DESC") # calculated rank of the hit rank = func.ts_rank_cd( weights, func.to_tsvector('english', citations.c.abstract), tsquery, normalization).label('rank') query = query.add_column(rank) if kwargs.get('snippet'): # configuration of the preview snippet headline_conf = 'StartSel=[, StopSel=], MaxWords={0}, MinWords={1}'.format( max_words, min_words) # function to create a preview snippet of the matched area snippet = func.ts_headline('english', citations.c.abstract, tsquery, headline_conf).label('snippet') # add snippet column to query query = query.add_column(snippet) return query
def filter_field(self, field, op, value): """ Applies a filter on a field. Notes on 'ne' op: Example data: [None, 'john', 'roger'] ne:john would return only roger (i.e. nulls excluded) ne: would return john and roger Notes on 'search' op: For some reason, SQLAlchemy uses to_tsquery rather than plainto_tsquery for the match operator to_tsquery uses operators (&, |, ! etc.) while plainto_tsquery tokenises the input string and uses AND between tokens, hence plainto_tsquery is what we want here For other database back ends, the behaviour of the match operator is completely different - see: http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html :param field: field name :param op: 'eq', 'ne', 'gt', 'lt', 'ge', 'le' or 'search' :param value: comparison value, string or list/tuple :return: """ app = self.app odm = app.odm() query = self.sql_query if field.model: field_model = self.app.models.get(field.model) if not field_model: return db_model = field_model.db_model() field = getattr(db_model, field.name, None) if field is None: field = getattr(db_model, field_model.id_field, None) if not field: return if field_model.identifier not in self.joins: self.joins.add(field_model.identifier) query = query.join(db_model) elif isinstance(field.field, str): field = getattr(self.model.db_model(), field.name, None) else: field = None if not field: return multiple = isinstance(value, (list, tuple)) if value == '': value = None if multiple and op in ('eq', 'ne'): if op == 'eq': query = query.filter(field.in_(value)) elif op == 'ne': query = query.filter(~field.in_(value)) else: if multiple: assert len(value) > 0 value = value[0] if op == 'eq': query = query.filter(field == value) elif op == 'ne': query = query.filter(field != value) elif op == 'search': dialect_name = odm.binds[odm[self.name].__table__].dialect.name if dialect_name == 'postgresql': ts_config = field.info.get( 'text_search_config', app.config['DEFAULT_TEXT_SEARCH_CONFIG']) query = query.filter( func.to_tsvector(ts_config, cast(field, String)).op('@@')( func.plainto_tsquery(value))) else: query = query.filter(field.match(value)) elif op == 'gt': query = query.filter(field > value) elif op == 'ge': query = query.filter(field >= value) elif op == 'lt': query = query.filter(field < value) elif op == 'le': query = query.filter(field <= value) self.sql_query = query return self
def filter_field(self, field, op, value): """ Applies a filter on a field. Notes on 'ne' op: Example data: [None, 'john', 'roger'] ne:john would return only roger (i.e. nulls excluded) ne: would return john and roger Notes on 'search' op: For some reason, SQLAlchemy uses to_tsquery rather than plainto_tsquery for the match operator to_tsquery uses operators (&, |, ! etc.) while plainto_tsquery tokenises the input string and uses AND between tokens, hence plainto_tsquery is what we want here For other database back ends, the behaviour of the match operator is completely different - see: http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html :param field: field name :param op: 'eq', 'ne', 'gt', 'lt', 'ge', 'le' or 'search' :param value: comparison value, string or list/tuple :return: """ app = self.app odm = app.odm() query = self.sql_query if field.model: field_model = self.app.models.get(field.model) if not field_model: return db_model = field_model.db_model() field = getattr(db_model, field.name, None) if field is None: field = getattr(db_model, field_model.id_field, None) if not field: return if field_model.identifier not in self.joins: self.joins.add(field_model.identifier) query = query.join(db_model) elif isinstance(field.field, str): field = getattr(self.model.db_model(), field.name, None) else: field = None if not field: return multiple = isinstance(value, (list, tuple)) if value == '': value = None if multiple and op in ('eq', 'ne'): if op == 'eq': query = query.filter(field.in_(value)) elif op == 'ne': query = query.filter(~field.in_(value)) else: if multiple: assert len(value) > 0 value = value[0] if op == 'eq': query = query.filter(field == value) elif op == 'ne': query = query.filter(field != value) elif op == 'search': dialect_name = odm.binds[odm[self.name].__table__].dialect.name if dialect_name == 'postgresql': ts_config = field.info.get( 'text_search_config', app.config['DEFAULT_TEXT_SEARCH_CONFIG'] ) query = query.filter( func.to_tsvector(ts_config, cast(field, String)).op( '@@')(func.plainto_tsquery(value)) ) else: query = query.filter(field.match(value)) elif op == 'gt': query = query.filter(field > value) elif op == 'ge': query = query.filter(field >= value) elif op == 'lt': query = query.filter(field < value) elif op == 'le': query = query.filter(field <= value) self.sql_query = query return self
def get_projects(id=None): ''' Regular response option for projects. ''' filters, querystring = get_query_params(request.args) if id: # Get one named project. filter = Project.id == id proj = db.session.query(Project).filter(filter).first() if proj: return jsonify(proj.asdict(True)) else: # If no project found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of projects. query = db.session.query(Project).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' include_issues = False ordering = None for attr, value in filters.iteritems(): if 'organization' in attr: org_attr = attr.split('_')[1] # Support searching for multiple org_types if "," in value: values = [unicode(item) for item in value.split(",")] # build a list of ilike queries to match on ilike_values = [getattr(Organization, org_attr).ilike(format_ilike_term(value)) for value in values] query = query.join(Project.organization).filter(or_(*ilike_values)) else: query = query.join(Project.organization).filter(getattr(Organization, org_attr).ilike(format_ilike_term(value))) elif 'q' in attr: # Returns all results if the value is empty if value: query = query.filter('project.tsv_body @@ plainto_tsquery(:search_query)').params(search_query=value) relevance_ordering_filter = func.ts_rank(Project.tsv_body, func.plainto_tsquery(value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if value == 'relevance': ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if value == 'asc': ordering_dir = 'asc' else: ordering_dir = 'desc' elif 'include_issues' in attr: if value in ['True','true','t']: include_issues = True else: query = query.filter(getattr(Project, attr).ilike(format_ilike_term(value))) if ordering_filter_name == 'last_updated': ordering_filter = last_updated_ordering_filter elif ordering_filter_name == 'relevance' and dir(relevance_ordering_filter) != dir(None): ordering_filter = relevance_ordering_filter if ordering_dir == 'desc': ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query=query, include_args=dict(include_organization=True, include_issues=include_issues), page=int(request.args.get('page', 1)), per_page=int(request.args.get('per_page', 10)), querystring=querystring) return jsonify(response)
def get_projects(id=None): ''' Regular response option for projects. ''' filters, querystring = get_query_params(request.args) if id: # Get one named project. filter = Project.id == id proj = db.session.query(Project).filter(filter).first() if proj: return jsonify(proj.asdict(True)) else: # If no project found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of projects. query = db.session.query(Project).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' ordering = None for attr, value in filters.iteritems(): if 'organization' in attr: org_attr = attr.split('_')[1] query = query.join(Project.organization).filter(getattr(Organization, org_attr).ilike('%%%s%%' % value)) elif 'q' in attr: # Returns all results if the value is empty if value: query = query.filter("project.tsv_body @@ plainto_tsquery('%s')" % value) relevance_ordering_filter = func.ts_rank(Project.tsv_body, func.plainto_tsquery('%s' % value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if(value == 'relevance'): ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if(value == 'asc'): ordering_dir = 'asc' else: ordering_dir = 'desc' else: query = query.filter(getattr(Project, attr).ilike('%%%s%%' % value)) if(ordering_filter_name == 'last_updated'): ordering_filter = last_updated_ordering_filter elif(ordering_filter_name == 'relevance' and dir(relevance_ordering_filter) != dir(None)): ordering_filter = relevance_ordering_filter if(ordering_dir == 'desc'): ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring) return jsonify(response)
def get_projects(id=None): ''' Regular response option for projects. ''' filters, querystring = get_query_params(request.args) if id: # Get one named project. filter = Project.id == id proj = db.session.query(Project).filter(filter).first() if proj: return jsonify(proj.asdict(True)) else: # If no project found return jsonify({"status": "Resource Not Found"}), 404 # Get a bunch of projects. query = db.session.query(Project).options(defer('tsv_body')) # Default ordering of results last_updated_ordering_filter = Project.last_updated relevance_ordering_filter = None ordering_filter_name = 'last_updated' ordering_filter = last_updated_ordering_filter ordering_dir = 'desc' include_issues = False ordering = None for attr, value in filters.iteritems(): if 'organization' in attr: org_attr = attr.split('_')[1] # Support searching for multiple org_types if "," in value: values = [unicode(item) for item in value.split(",")] # build a list of ilike queries to match on ilike_values = [ getattr(Organization, org_attr).ilike(format_ilike_term(value)) for value in values ] query = query.join(Project.organization).filter( or_(*ilike_values)) else: query = query.join(Project.organization).filter( getattr(Organization, org_attr).ilike(format_ilike_term(value))) elif 'q' in attr: # Returns all results if the value is empty if value: query = query.filter( 'project.tsv_body @@ plainto_tsquery(:search_query)' ).params(search_query=value) relevance_ordering_filter = func.ts_rank( Project.tsv_body, func.plainto_tsquery(value)) ordering_filter_name = 'relevance' elif 'only_ids' in attr: query = query.with_entities(Project.id) elif 'sort_by' in attr: if value == 'relevance': ordering_filter_name = 'relevance' else: ordering_filter_name = 'last_updated' elif 'sort_dir' in attr: if value == 'asc': ordering_dir = 'asc' else: ordering_dir = 'desc' elif 'include_issues' in attr: if value in ['True', 'true', 't']: include_issues = True else: query = query.filter( getattr(Project, attr).ilike(format_ilike_term(value))) if ordering_filter_name == 'last_updated': ordering_filter = last_updated_ordering_filter elif ordering_filter_name == 'relevance' and dir( relevance_ordering_filter) != dir(None): ordering_filter = relevance_ordering_filter if ordering_dir == 'desc': ordering = ordering_filter.desc() else: ordering = ordering_filter.asc() query = query.order_by(ordering) response = paged_results(query=query, include_args=dict(include_organization=True, include_issues=include_issues), page=int(request.args.get('page', 1)), per_page=int(request.args.get('per_page', 10)), querystring=querystring) return jsonify(response)