Zero search results searching private messages for specific keyword

bug
1

As an admin attempting to search private messages for a discussion on “natwest”…

Using the search in:private natwest returns zero results.

Running the following query returns 9 results:

SELECT COUNT(*) FROM posts p
LEFT JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.raw ILIKE '% natwest %'

Checking the actual posts they do contain text like “… its a natwest account …” and “… I pick up the guarantee on the Natwest overdraft …”

When using the search in:private car I see results as expected.

Is there a reason why I might not be seeing search results for the “natwest” keyword?

Currently running Discourse 1.7.0.beta1 - 823a699d41974affe42adbd562cc9fbfd5a56ef1.

2

A question for @sam, what is the repro? That specific word? Permissions weird?

3

Have a look at the query that is running via mini profiler

2 Likes
4

Thanks for the pointer I’ll take a look in mini profiler tomorrow and get some more detail

5

The actual query for “natwest” shown in mini profiler is as follows:

SELECT "posts".* FROM "posts" JOIN (SELECT *, row_number() over() row_number FROM (SELECT  topics.id, min(post_number) post_number FROM "posts" INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT JOIN categories ON categories.id = topics.category_id WHERE ("posts"."deleted_at" IS NULL) AND "posts"."post_type" IN (1, 2, 3, 4) AND "topics"."deleted_at" IS NULL AND (topics.visible) AND (topics.archetype =  'private_message') AND (post_search_data.search_data @@ TO_TSQUERY('english', '''natwest'':*')) AND (topics.id IN (SELECT topic_id
                                               FROM topic_allowed_users
                                               WHERE user_id = 1
                                               UNION ALL
                                               SELECT tg.topic_id
                                               FROM topic_allowed_groups tg
                                               JOIN group_users gu ON gu.user_id = 1 AND
                                                                        gu.group_id = tg.group_id)) AND ((categories.id IS NULL) OR (NOT categories.read_restricted) OR (categories.id IN (2,4,35,38,40,42,46,47,48,49,52,56,57,58,59,60,61,62,63,64,65,66,67,68,72,73,75,79,80,81,82,83,84,85,86,87,89,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,113,114,115,116,118,119))) GROUP BY topics.id  ORDER BY TS_RANK_CD(TO_TSVECTOR('english', topics.title), TO_TSQUERY('english', '''natwest'':*')) DESC, SUM(TS_RANK_CD(post_search_data.search_data, TO_TSQUERY('english', '''natwest'':*'))) DESC, topics.bumped_at DESC LIMIT 50) xxx) x ON x.id = posts.topic_id AND x.post_number = posts.post_number WHERE ("posts"."deleted_at" IS NULL)  ORDER BY row_number

Completing a side-by-side comparison the only differences between “car” and “natwest” queries only that keyword changes.

Editing the query further removing the ORDER BY statement (confirming it still returns no results)…

… and then removing AND (post_search_data.search_data @@ TO_TSQUERY('english', '''natwest'':*')) then returns results.

A general search of public content (i.e. not limiting via in:private) for “natwest” returns results.

Is this helpful to you?

6

Somehow I don’t think that should be in there if we’re searching private mesages.

7

Although this isn’t the problem as removing the entire AND statement makes no difference to getting the query to return results:

AND (
				(categories.id IS NULL)
				OR (NOT categories.read_restricted)
				OR (
					categories.id IN (
						2
						,4
						,35
						,38
						,40
						,42
                                                ...
						)
					)
				)
8

Are you still seeing this on latest?

9

TLDR: You can close this bug report.

(thanks for your support)

Search UI Improved

In the time since the original report the definition of “in:private” has changed (became more clear)…

… the new “Advanced Search” UI for search makes it clear to the user that the “in:private” search parameter will only search “my messages” opposed to “private messages” which previously didn’t indicate the search was limited to only my private messages.

Original Problem

At the time of this original report I was attempting to follow up on tracking an abusive person communicating privately under with multiple usernames - so it was import to use search to search across all private messages for many keywords to track this users activity.

Temporary Solution

I ended up creating custom queries via the Data Explorer plugin to solve my problem.

Possible Future Feature Request

I can see that an admin having the ability to directly search “all private messages” including those they are not a part of as an important admin tool for specific cases.

6 Likes