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_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 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 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, 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 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, 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, 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)
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
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