时间戳

首页 > 解决方案 > Is there a way to compare a list of values with a SQLite table?

android - Is there a way to compare a list of values with a SQLite table?

问题描述

I need this solution for my Android app that uses Room library for persisting data. I need to implement a solution where a list of string values is passed on by the user, it is compared with the data in the table and then returned back with both matched and unmatched results.

So, in a sense I want all the string values given by the user returned back no matter if they got matched with the table values or not. The matched values however would have more data from the table and unmatched values would be just values.

For example, I have a tasks table having columns namely task_id, task_title, task_status. This table has the following data:

1, 'Task 1', 'In process'

2, 'Task 2', 'On hold'

3, 'Task 3', 'Completed'

4, 'Task 4', 'In process'

Now user passes on a list of values to see if there is any data available against any of these or not. If there is a match, they would like to be presented with the status of that task. This list is passed in the form of a String[] and can contain any values and any number of values. Let us suppose that the string array is as follows:

{'Task 2', 'Task 3', 'Task 5'}

What sort of a query would be required that returns the following results:

'Task 2', 'On hold'

'Task 3', 'Completed'

'Task 5', Not available (or any other or no message)

So, I need all of the values passes on through the string array returned with data against those that got matched and no data (or a message) against the ones that didn't match.

What I have already tried are queries in the following form:

@Query("SELECT task_title, task_status FROM tasks WHERE task_title IN (:values)") LiveData<List<Task>> getTasks(String[] values);

It would return the following as per above example:

'Task 2', 'On hold'

'Task 3', 'Completed'

It doesn't return the unmatched value 'Task 5' which actually is required to be returned.

Any help would be greatly appreciated, thanks!

标签： androidsqliteandroid-room

解决方案

不带直SELECT。在 sqlite 中没有直接（阅读：简单）的方法来完成此操作。这:values是 sqlite 的“未知”。您是否考虑过根据 sql 结果和输入值之间的差异，将不匹配的结果添加到返回的对象中？

如果 安装了JSON1 扩展，您可以尝试如下操作：

SELECT value,  
CASE when t.task_status is null then "NOT FOUND" else t.task_status END status
FROM json_each(json_array(:values))
LEFT join tasks t on t.task_title = value

另一种选择可能是将每个输入值保存在一行中的表，因此可以应用相同的 JOIN 策略。

底线是输入列表必须“标记化”为单个元素，以便可以确定每个元素的状态。

推荐阅读