I have a table with events, and want to select all events which happen from three days ago. (The events do belong to categories which is a field in the Article table which has to description etc., but that works).
This is my code:
public List<Event> findByCategory(long catId, int start, int count) throws Exception {
String limit = "";
if (count > 0) limit = start + "," + count;
SQLiteQueryBuilder _QB = new SQLiteQueryBuilder();
_QB.setTables("Event e INNER JOIN Article a ON e.articleId=a.id");
String[] rows = _fields.keySet().toArray(new String[0]);
for (int i = 0; i < rows.length; i++) {
rows[i] = "e." + rows[i];
}
Cursor cursor = _QB.query(_db, rows, "date(e.startTime) > date('now','-3 days') AND EXISTS(SELECT * FROM CategoryArticleLink WHERE CategoryArticleLink.articleId = a.id AND EXISTS (SELECT * FROM Category WHERE Category.id = CategoryArticleLink.categoryId AND Category.id = '" + catId + "'))", null, null, null, "e.startTime ASC", limit);
List<Event> list = new ArrayList<>(cursor.getCount());
if (cursor.moveToFirst()) {
do {
Event item = getObject(cursor);
list.add(item);
} while (cursor.moveToNext());
}
cursor.close();
return list;
}
The resulting Query is:
SQLiteQuery: SELECT e.articleId, e.keynote, e.locationId, e.id, e.finishTime, e.startTime, e.languageCode, e.flag, e.lastUpdate FROM Event e INNER JOIN Article a ON e.articleId=a.id WHERE (date(e.startTime) > date('now','-3 days') AND EXISTS(SELECT * FROM CategoryArticleLink WHERE CategoryArticleLink.articleId = a.id AND EXISTS (SELECT * FROM Category WHERE Category.id = CategoryArticleLink.categoryId AND Category.id = '9'))) ORDER BY e.startTime ASC LIMIT 0,8
This returns an empty result set. However, if I take the date selection (date(e.startTime) > date('now','-3 days')) out of the query it returns all records as expected. So I must be doing something small wrong, but I just don't see what.
The field type on database generation is 'DATE', so that should be ok I expect.
Aucun commentaire:
Enregistrer un commentaire