CODEWITHSUNDEEP
androidsqlfull-text-searchandroid-room
Shadow
Shadow

Reputation: 4763

How to search for any combination of one or more words, regardless of their order using Android Room

I have been able to achieve a basic search capability in Android Room + FTS with the following query as an example in my Dao:

@Query("SELECT * FROM Conversation JOIN ConversationFts ON Conversation.id == ConversationFts.id WHERE ConversationFts.title LIKE :text GROUP BY Conversation.id")
public abstract DataSource.Factory<Integer, Conversation> search(String text);

Where the text is passed along between percentage characters, as such %lorem%.

This example works perfectly for the search of a single word and I want to expand this to be able to search for one or more words with the condition that they do not need to be in the order they are entered, but they must have a match for all the words. This means this has to be an AND and not OR situation.

I've found a few examples of SQL queries, but they require a specific query tailored for each case, for example: LIKE text AND LIKE another AND LIKE, etc... which is not a solution.

How can this be achieved? And to make it clear, I am not looking for a raw query solution, I want to stick to Room as much as possible, otherwise, I'd rather just use this as it is than to resort to raw queries.

EDIT: Adding an example per request

I search for do the results returned include all matches that contain do in the title, even if it is a partial match

I search for do and test the results returned include all matches that contain do and test, but in no specific order, even if they are partial matches.

However, if just one of them cannot be found in the text then it will not be returned in the results. For example, if do is found, but test is not then it will not be part of the results.

Upvotes: 1

Views: 82

Answers (1)

aminography
aminography

Reputation: 22832

I think there is no way to do that except creating a raw query dynamically. You can write another method, something like this:

public abstract class ConversationDao {

    public DataSource.Factory<Integer, Conversation> search(String text) {
        StringBuilder builder = new StringBuilder();
        String[] words = text.split("\\s+");

        if (words.length > 0) {
            builder.append(" WHERE ");
        }

        for (int i = 0; i < words.length; i++) {
            builder.append("ConversationFts.title LIKE %").append(words[i]).append("%");
            if (i < words.length - 1) {
                builder.append(" AND ");
            }
        }

        SupportSQLiteQuery query = new SimpleSQLiteQuery(
                "SELECT * FROM Conversation JOIN ConversationFts ON Conversation.id == ConversationFts.id"
                        + builder.toString()
                        + " GROUP BY Conversation.id"
        );
        
        return search(query);
    }

    @RawQuery
    public abstract DataSource.Factory<Integer, Conversation> search(SupportSQLiteQuery query);

}

Upvotes: 2

Related Questions