Signal-android: Secondarily sort search results by date doesn't seem to work

[uspoma]

• [x] I have searched open and closed issues for duplicates
• [x] I am submitting a bug report for existing functionality that does not work as intended
• [x] I have read https://github.com/signalapp/Signal-Android/wiki/Submitting-useful-bug-reports
• [x] This isn't a feature request or a discussion topic

---

Bug description

Secondarily sort search results by date doesn't seem to work. The search results are still mixed in terms of date

Steps to reproduce

• enter a word to search for
• get search results
  -> They are still mixed in terms of date

Actual result: The search results are not sorted by date
Expected result: The search results should be sorted by date

Screenshots

Device info

Signal version: 4.20.4 until commit 0503c9eea5a7d0315fc02ecc204d90b68980ea0f

[greyson-signal]

Search results are still primarily sorted by rank, which is determined by the closeness of the actual text match. Only results of the _same rank_ are sorted by date. Is there a specific situation where you feel that two text matches should be equally ranked in closeness but are still not ordered chronologically? I guess the larger question is, is there anything specific you can report about search results that you think are bad?

[ghost]

@greyson-signal imo, then 'rank' in https://github.com/signalapp/Signal-Android/commit/71ccbf2a1b9deac0403ac63ebe9723755fe0c13d#diff-8dc433251887e8832d0d566e19a9c74cR76 should be DESC rather then ASC:
"ORDER BY rank ASC, " + MmsSmsColumns.NORMALIZED_DATE_RECEIVED + " DESC " +

[greyson-signal]

@psm14 Smaller ranks mean closer matches :) https://www.sqlite.org/fts5.html#the_bm25_function

[greyson-signal]

FWIW I may move to just sorting by date exclusively. The way SQLite FTS works, it never gives back "bad matches", so to speak. Every result will contain your query in some form. Meaning it's probably safe to sort by date and be done with it, since I think more recent results are more relevant overall.

[ghost]

@greyson-signal from the same link:

To sort results by relevance (most to least relevant), an ORDER BY may be added to a full-text query as follows:

-- Query for all rows that contain at least once instance of the term
-- "fts5" (in any column). Return results in order from best to worst
-- match.  
SELECT * FROM email WHERE email MATCH 'fts5' ORDER BY rank;

since I think more recent results are more relevant overall.

you can invert DATE_RECEIVED as primarily and RANK as secondarily

[greyson-signal]

@p4nci Given that DATE_RECEIVED is a timestamp in milliseconds, the cost of calculating the rank is likely way higher than the benefit of sorting messages you received within the same millisecond :)

[greyson-signal]

Switched to sorting exclusively by date in 5650a02cfbd8f0d29ec6a6963483a228c3712b90.

[uspoma]

@greyson-signal

Search results are still primarily sorted by rank, which is determined by the closeness of the actual text match.

I have to ask to get that right: by "closeness of the actual text match" you mean if the word I search for is the exact same word and not just part of another word?

Only results of the same rank are sorted by date.

So that means if I for example search for "Internet" only results that exactly match that word are sorted by date?

Is there a specific situation where you feel that two text matches should be equally ranked in closeness but are still not ordered chronologically? I guess the larger question is, is there anything specific you can report about search results that you think are bad?

Yes, for me this does not seem to work. Let's stick with this "Internet" example. Below you find the representation of my search results when searching for the word Internet:

1. Internetbrowser , April 14.
2. Internet, Oct. 26.
3. Internetbrowser, July 28.
4. Internet, Tue
5. internet (mind the lower case this time), Tue
6. Internet, Tue
7. Internet,  July 27.
8. Internetbrowser, July 26.
9. Internetbrowser, Tue
10. internetbrowser, Jul 28.
11. Internet, Jul 26.
12. Internet, Jun 28.
13. Internet. (mind the dot), Aug 26.
14. Internetbrowser, Jul 28.
15. Internet, Jun 6
16. Internet, Tue
17. Internet, Tue

Sorry, but this does not make sense to me.

Edit: You switched the sorting logic with a new commit. My posting was to slow.

[greyson-signal]

@uspoma Yes I did switch it, but thank you for the very detailed example!