def filter_queryset(self, request, queryset, view): # NOTE: See migtration 0033_text_search_indexes q = request.QUERY_PARAMS.get('q', None) if q: tsquery = "to_tsquery('english_nostop', %s)" tsquery_params = [to_tsquery(q)] tsvector = """ setweight(to_tsvector('english_nostop', coalesce(projects_project.name, '')), 'A') || setweight(to_tsvector('english_nostop', coalesce(inmutable_array_to_string(projects_project.tags), '')), 'B') || setweight(to_tsvector('english_nostop', coalesce(projects_project.description, '')), 'C') """ select = { "rank": "ts_rank({tsvector},{tsquery})".format(tsquery=tsquery, tsvector=tsvector), } select_params = tsquery_params where = ["{tsvector} @@ {tsquery}".format(tsquery=tsquery, tsvector=tsvector),] params = tsquery_params order_by = ["-rank", ] queryset = queryset.extra(select=select, select_params=select_params, where=where, params=params, order_by=order_by) return queryset
def _search_by_query(queryset, tsquery, tsvector, text): select = { "rank": "ts_rank({tsvector},{tsquery})".format(tsquery=tsquery, tsvector=tsvector), } order_by = ["-rank", ] where = ["{tsvector} @@ {tsquery}".format(tsquery=tsquery, tsvector=tsvector), ] if text: queryset = queryset.extra(select=select, select_params=[to_tsquery(text)], where=where, params=[to_tsquery(text)], order_by=order_by) queryset = attach_total_points(queryset) return queryset[:MAX_RESULTS]
def search_issues(project, text): model_cls = apps.get_model("issues", "Issue") where_clause = ("to_tsvector('english_nostop', coalesce(issues_issue.subject) || ' ' || " "coalesce(issues_issue.ref) || ' ' || " "coalesce(issues_issue.description, '')) @@ to_tsquery('english_nostop', %s)") if text: return (model_cls.objects.extra(where=[where_clause], params=[to_tsquery(text)]) .filter(project_id=project.pk)[:MAX_RESULTS]) return model_cls.objects.filter(project_id=project.pk)[:MAX_RESULTS]
def search_wiki_pages(project, text): model_cls = apps.get_model("wiki", "WikiPage") where_clause = ("to_tsvector('english_nostop', coalesce(wiki_wikipage.slug) || ' ' || " "coalesce(wiki_wikipage.content, '')) " "@@ to_tsquery('english_nostop', %s)") if text: return (model_cls.objects.extra(where=[where_clause], params=[to_tsquery(text)]) .filter(project_id=project.pk)[:MAX_RESULTS]) return model_cls.objects.filter(project_id=project.pk)[:MAX_RESULTS]
def filter_queryset(self, request, queryset, view): q = request.QUERY_PARAMS.get('q', None) if q: table = queryset.model._meta.db_table where_clause = ("to_tsvector('english_nostop', coalesce({table}.subject, '') || ' ' || " "coalesce({table}.ref) || ' ' || " "coalesce({table}.description, '')) @@ to_tsquery('english_nostop', %s)".format(table=table)) queryset = queryset.extra(where=[where_clause], params=[to_tsquery(q)]) return queryset
def filter_queryset(self, user, request, queryset, view): qs = user.contacts_visible_by_user(request.user) q = request.QUERY_PARAMS.get('q', None) if q: table = qs.model._meta.db_table where_clause = (""" to_tsvector('simple', coalesce({table}.username, '') || ' ' || coalesce({table}.full_name) || ' ' || coalesce({table}.email, '')) @@ to_tsquery('simple', %s) """.format(table=table)) qs = qs.extra(where=[where_clause], params=[to_tsquery(q)]) return qs.distinct()
def filter_queryset(self, request, queryset, view): q = request.QUERY_PARAMS.get('q', None) if q: table = queryset.model._meta.db_table where_clause = (""" to_tsvector('simple', coalesce({table}.subject, '') || ' ' || coalesce(array_to_string({table}.tags, ' '), '') || ' ' || coalesce({table}.ref) || ' ' || coalesce({table}.description, '')) @@ to_tsquery('simple', %s) """.format(table=table)) queryset = queryset.extra(where=[where_clause], params=[to_tsquery(q)]) return queryset
def filter_queryset(self, user, request, queryset, view): qs = user.contacts_visible_by_user(request.user) q = request.QUERY_PARAMS.get('q', None) if q: table = qs.model._meta.db_table where_clause = (""" to_tsvector('english_nostop', coalesce({table}.username, '') || ' ' || coalesce({table}.full_name) || ' ' || coalesce({table}.email, '')) @@ to_tsquery('english_nostop', %s) """.format(table=table)) qs = qs.extra(where=[where_clause], params=[to_tsquery(q)]) return qs.distinct()
def filter_queryset(self, user, request, queryset, view): qs = user.contacts_visible_by_user(request.user) exclude_project = request.QUERY_PARAMS.get("exclude_project", None) if exclude_project: qs = qs.exclude(projects__id=exclude_project) q = request.QUERY_PARAMS.get("q", None) if q: table = qs.model._meta.db_table where_clause = """ to_tsvector('simple', coalesce({table}.username, '') || ' ' || coalesce({table}.full_name) || ' ' || coalesce({table}.email, '')) @@ to_tsquery('simple', %s) """.format(table=table) qs = qs.extra(where=[where_clause], params=[to_tsquery(q)]) return qs.distinct()
def filter_queryset(self, request, queryset, view): # NOTE: See migtration 0033_text_search_indexes q = request.QUERY_PARAMS.get("q", None) if q: tsquery = "to_tsquery('simple', %s)" tsquery_params = [to_tsquery(q)] tsvector = """ setweight(to_tsvector('simple', coalesce(projects_project.name, '')), 'A') || setweight(to_tsvector('simple', coalesce(inmutable_array_to_string(projects_project.tags), '')), 'B') || setweight(to_tsvector('simple', coalesce(projects_project.description, '')), 'C') """ select = { "rank": "ts_rank({tsvector},{tsquery})".format(tsquery=tsquery, tsvector=tsvector), } select_params = tsquery_params where = [ "{tsvector} @@ {tsquery}".format(tsquery=tsquery, tsvector=tsvector), ] params = tsquery_params order_by = [ "-rank", ] queryset = queryset.extra( select=select, select_params=select_params, where=where, params=params, order_by=order_by, ) return queryset
def get_voted_list(for_user, from_user, type=None, q=None): filters_sql = "" and_needed = False if type: filters_sql += " AND type = %(type)s " if q: filters_sql += """ AND ( to_tsvector('english_nostop', coalesce(subject,'') || ' ' ||coalesce(entities.name,'') || ' ' ||coalesce(to_char(ref, '999'),'')) @@ to_tsquery('english_nostop', %(q)s) ) """ sql = """ -- BEGIN Basic info: we need to mix info from different tables and denormalize it SELECT entities.*, projects_project.name as project_name, projects_project.description as description, projects_project.slug as project_slug, projects_project.is_private as project_is_private, projects_project.tags_colors, users_user.username assigned_to_username, users_user.full_name assigned_to_full_name, users_user.photo assigned_to_photo, users_user.email assigned_to_email FROM ( {userstories_sql} UNION {tasks_sql} UNION {issues_sql} ) as entities -- END Basic info -- BEGIN Project info LEFT JOIN projects_project ON (entities.project = projects_project.id) -- END Project info -- BEGIN Assigned to user info LEFT JOIN users_user ON (assigned_to = users_user.id) -- END Assigned to user info -- BEGIN Permissions checking LEFT JOIN projects_membership -- Here we check the memberbships from the user requesting the info ON (projects_membership.user_id = {from_user_id} AND projects_membership.project_id = entities.project) LEFT JOIN users_role ON (entities.project = users_role.project_id AND users_role.id = projects_membership.role_id) WHERE -- public project ( projects_project.is_private = false OR( -- private project where the view_ permission is included in the user role for that project or in the anon permissions projects_project.is_private = true AND( (entities.type = 'issue' AND 'view_issues' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) OR (entities.type = 'task' AND 'view_tasks' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) OR (entities.type = 'userstory' AND 'view_us' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) ) )) -- END Permissions checking {filters_sql} ORDER BY entities.created_date DESC; """ from_user_id = -1 if not from_user.is_anonymous(): from_user_id = from_user.id sql = sql.format(for_user_id=for_user.id, from_user_id=from_user_id, filters_sql=filters_sql, userstories_sql=_build_sql_for_type( for_user, "userstory", "userstories_userstory", "votes_vote", slug_column="null"), tasks_sql=_build_sql_for_type(for_user, "task", "tasks_task", "votes_vote", slug_column="null"), issues_sql=_build_sql_for_type(for_user, "issue", "issues_issue", "votes_vote", slug_column="null")) cursor = connection.cursor() params = {"type": type, "q": to_tsquery(q) if q is not None else ""} cursor.execute(sql, params) desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ]
def get_liked_list(for_user, from_user, type=None, q=None): filters_sql = "" and_needed = False if type: filters_sql += " AND type = '{type}' ".format(type=type) if q: filters_sql += """ AND ( to_tsvector('english_nostop', coalesce(subject,'') || ' ' ||coalesce(entities.name,'') || ' ' ||coalesce(to_char(ref, '999'),'')) @@ to_tsquery('english_nostop', '{q}') ) """.format(q=to_tsquery(q)) sql = """ -- BEGIN Basic info: we need to mix info from different tables and denormalize it SELECT entities.*, projects_project.name as project_name, projects_project.description as description, projects_project.slug as project_slug, projects_project.is_private as project_is_private, projects_project.tags_colors, users_user.username assigned_to_username, users_user.full_name assigned_to_full_name, users_user.photo assigned_to_photo, users_user.email assigned_to_email FROM ( {projects_sql} ) as entities -- END Basic info -- BEGIN Project info LEFT JOIN projects_project ON (entities.project = projects_project.id) -- END Project info -- BEGIN Assigned to user info LEFT JOIN users_user ON (assigned_to = users_user.id) -- END Assigned to user info -- BEGIN Permissions checking LEFT JOIN projects_membership -- Here we check the memberbships from the user requesting the info ON (projects_membership.user_id = {from_user_id} AND projects_membership.project_id = entities.project) LEFT JOIN users_role ON (entities.project = users_role.project_id AND users_role.id = projects_membership.role_id) WHERE -- public project ( projects_project.is_private = false OR( -- private project where the view_ permission is included in the user role for that project or in the anon permissions projects_project.is_private = true AND( 'view_project' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions)) ) )) -- END Permissions checking {filters_sql} ORDER BY entities.created_date DESC; """ from_user_id = -1 if not from_user.is_anonymous(): from_user_id = from_user.id sql = sql.format( for_user_id=for_user.id, from_user_id=from_user_id, filters_sql=filters_sql, projects_sql=_build_liked_sql_for_projects(for_user)) cursor = connection.cursor() cursor.execute(sql) desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ]
def get_liked_list(for_user, from_user, type=None, q=None): filters_sql = "" if type: filters_sql += " AND type = %(type)s " if q: filters_sql += """ AND ( to_tsvector('simple', coalesce(subject,'') || ' ' ||coalesce(entities.name,'') || ' ' ||coalesce(to_char(ref, '999'),'')) @@ to_tsquery('simple', %(q)s) ) """ sql = """ -- BEGIN Basic info: we need to mix info from different tables and denormalize it SELECT entities.*, projects_project.name as project_name, projects_project.description as description, projects_project.slug as project_slug, projects_project.is_private as project_is_private, projects_project.blocked_code as project_blocked_code, projects_project.tags_colors, projects_project.logo, users_user.id as assigned_to_id, row_to_json(users_user) as assigned_to_extra_info FROM ( {projects_sql} ) as entities -- END Basic info -- BEGIN Project info LEFT JOIN projects_project ON (entities.project = projects_project.id) -- END Project info -- BEGIN Assigned to user info LEFT JOIN users_user ON (assigned_to = users_user.id) -- END Assigned to user info -- BEGIN Permissions checking LEFT JOIN projects_membership -- Here we check the memberbships from the user requesting the info ON (projects_membership.user_id = {from_user_id} AND projects_membership.project_id = entities.project) LEFT JOIN users_role ON (entities.project = users_role.project_id AND users_role.id = projects_membership.role_id) WHERE -- public project ( projects_project.is_private = false OR( -- private project where the view_ permission is included in the user role for that project or in the anon permissions projects_project.is_private = true AND( 'view_project' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions)) ) )) -- END Permissions checking {filters_sql} ORDER BY entities.created_date DESC; """ from_user_id = -1 if not from_user.is_anonymous(): from_user_id = from_user.id sql = sql.format(for_user_id=for_user.id, from_user_id=from_user_id, filters_sql=filters_sql, projects_sql=_build_liked_sql_for_projects(for_user)) cursor = connection.cursor() params = {"type": type, "q": to_tsquery(q) if q is not None else ""} cursor.execute(sql, params) desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ]
def get_voted_list(for_user, from_user, type=None, q=None): filters_sql = "" and_needed = False if type: filters_sql += " AND type = %(type)s " if q: filters_sql += """ AND ( to_tsvector('english_nostop', coalesce(subject,'') || ' ' ||coalesce(entities.name,'') || ' ' ||coalesce(to_char(ref, '999'),'')) @@ to_tsquery('english_nostop', %(q)s) ) """ sql = """ -- BEGIN Basic info: we need to mix info from different tables and denormalize it SELECT entities.*, projects_project.name as project_name, projects_project.description as description, projects_project.slug as project_slug, projects_project.is_private as project_is_private, projects_project.blocked_code as project_blocked_code, projects_project.tags_colors, projects_project.logo, users_user.username assigned_to_username, users_user.full_name assigned_to_full_name, users_user.photo assigned_to_photo, users_user.email assigned_to_email FROM ( {userstories_sql} UNION {tasks_sql} UNION {issues_sql} ) as entities -- END Basic info -- BEGIN Project info LEFT JOIN projects_project ON (entities.project = projects_project.id) -- END Project info -- BEGIN Assigned to user info LEFT JOIN users_user ON (assigned_to = users_user.id) -- END Assigned to user info -- BEGIN Permissions checking LEFT JOIN projects_membership -- Here we check the memberbships from the user requesting the info ON (projects_membership.user_id = {from_user_id} AND projects_membership.project_id = entities.project) LEFT JOIN users_role ON (entities.project = users_role.project_id AND users_role.id = projects_membership.role_id) WHERE -- public project ( projects_project.is_private = false OR( -- private project where the view_ permission is included in the user role for that project or in the anon permissions projects_project.is_private = true AND( (entities.type = 'issue' AND 'view_issues' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) OR (entities.type = 'task' AND 'view_tasks' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) OR (entities.type = 'userstory' AND 'view_us' = ANY (array_cat(users_role.permissions, projects_project.anon_permissions))) ) )) -- END Permissions checking {filters_sql} ORDER BY entities.created_date DESC; """ from_user_id = -1 if not from_user.is_anonymous(): from_user_id = from_user.id sql = sql.format( for_user_id=for_user.id, from_user_id=from_user_id, filters_sql=filters_sql, userstories_sql=_build_sql_for_type(for_user, "userstory", "userstories_userstory", "votes_vote", slug_column="null"), tasks_sql=_build_sql_for_type(for_user, "task", "tasks_task", "votes_vote", slug_column="null"), issues_sql=_build_sql_for_type(for_user, "issue", "issues_issue", "votes_vote", slug_column="null")) cursor = connection.cursor() params = { "type": type, "q": to_tsquery(q) if q is not None else "" } cursor.execute(sql, params) desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ]
def test_to_tsquery(): for (input, expected) in TS_QUERY_TRANSFORMATIONS: expected = re.sub("([0-9])", r"'\1':*", expected) actual = to_tsquery(input) assert actual == expected