def _by_search_tsearch(self, query: Query, operand: str,
maybe_negate: ConditionTransform) -> Query:
tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand))
query = query.column(ts_locs_array(literal("zulip.english_us_search"),
column("rendered_content"),
tsquery).label("content_matches"))
# We HTML-escape the topic in Postgres to avoid doing a server round-trip
query = query.column(ts_locs_array(literal("zulip.english_us_search"),
func.escape_html(topic_column_sa()),
tsquery).label("topic_matches"))
# Do quoted string matching. We really want phrase
# search here so we can ignore punctuation and do
# stemming, but there isn't a standard phrase search
# mechanism in Postgres
for term in re.findall(r'"[^"]+"|\S+', operand):
if term[0] == '"' and term[-1] == '"':
term = term[1:-1]
term = '%' + connection.ops.prep_for_like_query(term) + '%'
cond = or_(column("content").ilike(term),
topic_column_sa().ilike(term))
query = query.where(maybe_negate(cond))
cond = column("search_tsvector").op("@@")(tsquery)
return query.where(maybe_negate(cond))
def add_narrow_conditions(user_profile: UserProfile,
inner_msg_id_col: ColumnElement,
query: Query,
narrow: OptionalNarrowListT) -> Tuple[Query, bool]:
is_search = False # for now
if narrow is None:
return (query, is_search)
# Build the query for the narrow
builder = NarrowBuilder(user_profile, inner_msg_id_col)
search_operands = []
# As we loop through terms, builder does most of the work to extend
# our query, but we need to collect the search operands and handle
# them after the loop.
for term in narrow:
if term['operator'] == 'search':
search_operands.append(term['operand'])
else:
query = builder.add_term(query, term)
if search_operands:
is_search = True
query = query.column(topic_column_sa()).column(column("rendered_content"))
search_term = dict(
operator='search',
operand=' '.join(search_operands),
)
query = builder.add_term(query, search_term)
return (query, is_search)
def _by_search_pgroonga(self, query: Query, operand: str,
maybe_negate: ConditionTransform) -> Query:
match_positions_character = func.pgroonga_match_positions_character
query_extract_keywords = func.pgroonga_query_extract_keywords
operand_escaped = func.escape_html(operand)
keywords = query_extract_keywords(operand_escaped)
query = query.column(match_positions_character(column("rendered_content"),
keywords).label("content_matches"))
query = query.column(match_positions_character(func.escape_html(topic_column_sa()),
keywords).label("topic_matches"))
condition = column("search_pgroonga").op("&@~")(operand_escaped)
return query.where(maybe_negate(condition))
def messages_in_narrow_backend(
request: HttpRequest,
user_profile: UserProfile,
msg_ids: List[int] = REQ(validator=check_list(check_int)),
narrow: OptionalNarrowListT = REQ(converter=narrow_parameter),
) -> HttpResponse:
first_visible_message_id = get_first_visible_message_id(user_profile.realm)
msg_ids = [
message_id for message_id in msg_ids
if message_id >= first_visible_message_id
]
# This query is limited to messages the user has access to because they
# actually received them, as reflected in `zerver_usermessage`.
query = select(
[column("message_id"),
topic_column_sa(),
column("rendered_content")],
and_(
column("user_profile_id") == literal(user_profile.id),
column("message_id").in_(msg_ids)),
join(
table("zerver_usermessage"), table("zerver_message"),
literal_column("zerver_usermessage.message_id") == literal_column(
"zerver_message.id")))
builder = NarrowBuilder(user_profile, column("message_id"))
if narrow is not None:
for term in narrow:
query = builder.add_term(query, term)
sa_conn = get_sqlalchemy_connection()
query_result = list(sa_conn.execute(query).fetchall())
search_fields = dict()
for row in query_result:
message_id = row['message_id']
topic_name = row[DB_TOPIC_NAME]
rendered_content = row['rendered_content']
if 'content_matches' in row:
content_matches = row['content_matches']
topic_matches = row['topic_matches']
else:
content_matches = topic_matches = []
search_fields[message_id] = get_search_fields(rendered_content,
topic_name,
content_matches,
topic_matches)
return json_success({"messages": search_fields})
