英文:
How to find if a list contains any element of another list in sqlite
问题
我有一个 sqlite 表，其中有一列名为 selected_dates。此列包含了一个 CustomDates 对象的列表。我想查询这一列是否包含另一个相同类型的列表（customDates）中的任何项。但是我找不到任何方法。我尝试过：
@Query("SELECT * FROM schedule_table WHERE (type_daily IS 1) OR (:monthOfYear IS 1) " +
"OR (selectedDates IN (:customDates))" +
"ORDER BY scheduleID DESC")
LiveData<List<Schedule>> getSchedulesOfThisWeek(String monthOfYear, List<ScheduleType.Dates> customDates);
这是我的示例数据（List<Date> 对象以 json 格式存储）在 sqlite browser 中的数据图像：
[{ "dayOfMonth":1, "month":8 },{ "dayOfMonth":2, "month":8 },{ "dayOfMonth":3, "month":8 },{ "dayOfMonth":4, "month":8 },{ "dayOfMonth":5, "month":8 },{ "dayOfMonth":6, "month":8 },{ "dayOfMonth":7, "month":8 },{ "dayOfMonth":8, "month":8 },{ "dayOfMonth":9, "month":8 },{ "dayOfMonth":10, "month":8 },{ "dayOfMonth":11, "month":8 },{ "dayOfMonth":12, "month":8 },{ "dayOfMonth":13, "month":8 },{ "dayOfMonth":14, "month":8 },{ "dayOfMonth":15, "month":8 },{ "dayOfMonth":16, "month":8 },{ "dayOfMonth":17, "month":8 },{ "dayOfMonth":18, "month":8 },{ "dayOfMonth":19, "month":8 },{ "dayOfMonth":20, "month":8 },{ "dayOfMonth":21, "month":8 },{ "dayOfMonth":22, "month":8 },{ "dayOfMonth":23, "month":8 },{ "dayOfMonth":24, "month":8 },{ "dayOfMonth":25, "month":8 },{ "dayOfMonth":26, "month":8 },{ "dayOfMonth":27, "month":8 },{ "dayOfMonth":28, "month":8 },{ "dayOfMonth":29, "month":8 }]
简而言之，在 sqlite 中如何找出两个列表是否相交？
英文:
I have a sqlite table where there is a column selected_dates. This column contains a list of CustomDates object. I want to query if this column contains any item of other list of same type( customDates). But I can't find any way. I've tried:
@Query("SELECT * FROM schedule_table WHERE (type_daily IS 1) OR (:monthOfYear IS 1) " +
"OR (selectedDates IN (:customDates))" +
"ORDER BY scheduleID DESC")
LiveData<List<Schedule>> getSchedulesOfThisWeek(String monthOfYear, List<ScheduleType.Dates> customDates);
Here is my sample data (List<Date> object is stored in json format) Image of data in sqlite browser:
[{"dayOfMonth":1,"month":8},{"dayOfMonth":2,"month":8},{"dayOfMonth":3,"month":8},{"dayOfMonth":4,"month":8},{"dayOfMonth":5,"month":8},{"dayOfMonth":6,"month":8},{"dayOfMonth":7,"month":8},{"dayOfMonth":8,"month":8},{"dayOfMonth":9,"month":8},{"dayOfMonth":10,"month":8},{"dayOfMonth":11,"month":8},{"dayOfMonth":12,"month":8},{"dayOfMonth":13,"month":8},{"dayOfMonth":14,"month":8},{"dayOfMonth":15,"month":8},{"dayOfMonth":16,"month":8},{"dayOfMonth":17,"month":8},{"dayOfMonth":18,"month":8},{"dayOfMonth":19,"month":8},{"dayOfMonth":20,"month":8},{"dayOfMonth":21,"month":8},{"dayOfMonth":22,"month":8},{"dayOfMonth":23,"month":8},{"dayOfMonth":24,"month":8},{"dayOfMonth":25,"month":8},{"dayOfMonth":26,"month":8},{"dayOfMonth":27,"month":8},{"dayOfMonth":28,"month":8},{"dayOfMonth":29,"month":8}]
In a word, How to find if two List Intersects each other or not in sqlite?
答案1
得分: 1
基本上，Room Database以基本数据类型存储数据。因此，您选择的日期以TEXT格式存储。您可以执行LIKE操作以查找匹配的列。
Room还提供了一个注释@RawQuery。您可以使用这个。对于您的问题，这可能是一个解决方案：
在Dao接口中：
@RawQuery(observedEntities = Schedule.class)
LiveData<List<Schedule>> getSchedule(SupportSQLiteQuery query);
并且使用getSchedule()方法：
String query = "SELECT * FROM schedule_table WHERE " +
"(type_daily IS 1) OR (" + monthOfYear + " IS 1) OR " +
conditions + " ORDER BY scheduleID DESC";
return mDao.getSchedule(new SimpleSQLiteQuery(query));
其中condition是您想要查找的日期的String。例如：
String condition = "selectedDates LIKE '%{\"dayOfMonth\":1,\"month\":8}%' OR selectedDates LIKE '%{\"dayOfMonth\":5,\"month\":8}%';
您可以创建一个基于List<ScheduleType.Dates>生成此字符串的方法。
英文:
Basically Room Database Stores data in primary data types. So your selected dates are in TEXT format. You can perform LIKE operation to find the matching columns.
Room also provides a annotation @RawQuery. You can use this. For your problem this might be a solution:
in Dao interface:
@RawQuery(observedEntities = Schedule.class)
LiveData<List<Schedule>> getSchedule(SupportSQLiteQuery query);
And to use getSchedule():
String query = "SELECT * FROM schedule_table WHERE " +
"(type_daily IS 1) OR (" + monthOfYear + " IS 1) OR " +
conditions + " ORDER BY scheduleID DESC";
return mDao.getSchedule(new SimpleSQLiteQuery(query));
where condition is the String of the dates you want to find. For example:
String condition = "selectedDates LIKE '%{\"dayOfMonth\":1,\"month\":8}%' OR selectedDates LIKE '%{\"dayOfMonth\":5,\"month\":8}%';
You can create a method which will generate this string based on a List<ScheduleType.Dates>.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论