有没有一种方法可以将值列表与SQLite表进行比较? [英] Is there a way to compare a list of values with a SQLite table?

问题描述

我需要使用Room库保存数据的Android应用程序使用此解决方案.我需要实现一个解决方案，其中用户传递一个字符串值列表，并将其与表中的数据进行比较，然后返回匹配和不匹配的结果.

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.

例如，我有一个tasks表，该表的列分别为task_id，task_title，task_status.该表包含以下数据:

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

1，任务1"，处理中"

1, 'Task 1', 'In process'

2，任务2"，保留"

2, 'Task 2', 'On hold'

3，任务3"，已完成"

3, 'Task 3', 'Completed'

4，任务4"，处理中"

4, 'Task 4', 'In process'

现在，用户传递一个值列表，以查看是否有针对这些值的可用数据.如果存在匹配项，则希望向他们显示该任务的状态. 该列表以String[]的形式传递，并且可以包含任何值和任意数量的值.让我们假设字符串数组如下:

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:

任务2"，保留"

任务3"，已完成"

任务5"，不可用(或任何其他消息或无消息)

'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);

@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:

任务2"，保留"

任务3"，已完成"

它不会返回实际上需要返回的不匹配值任务5".

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

任何帮助将不胜感激，谢谢！

Any help would be greatly appreciated, thanks!

推荐答案

不是直的SELECT.在sqlite中没有简单的方法(阅读:简单)来完成此任务. :values是未知"字母.到sqlite.您是否考虑过根据sql结果和输入值之间的差异将不匹配的结果添加到返回的对象中?

Not with a straight SELECT. There is no straightforward (read: simple) way to accomplish this in sqlite. The :values is an "unknown" to sqlite. Have you considered adding the unmatched results to the returned object, based on the difference between the sql result and the input values?

如果已安装 JSON1扩展，则可以尝试以下操作:

If JSON1 extension is installed, you might try something like this:

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策略.

Another option might be a table that holds each input value in a row, so the same JOIN strategy can be applied.

最底行是输入列表必须为"tokenized"，分成各个元素，以便确定每个元素的状态.

The bottom line is the input list must be "tokenized" into individual elements so the status of each can be determined.

这篇关于有没有一种方法可以将值列表与SQLite表进行比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！