Filter the All Episodes tab via SQL Query

Fixes #4414

3 files changed, 95 insertions, 8 deletions

diff --git a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java
index e8e478a86..b9d79715a 100644
--- a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java
+++ b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java
@@ -8,6 +8,7 @@ import java.util.Arrays;
 import java.util.List;
 
 import de.danoeh.antennapod.core.storage.DBReader;
+import de.danoeh.antennapod.core.storage.PodDBAdapter;
 import de.danoeh.antennapod.core.util.LongList;
 
 import static de.danoeh.antennapod.core.feed.FeedItem.TAG_FAVORITE;
@@ -15,6 +16,7 @@ import static de.danoeh.antennapod.core.feed.FeedItem.TAG_FAVORITE;
 public class FeedItemFilter {
 
     private final String[] mProperties;
+    private final String mQuery;
 
     private boolean showPlayed = false;
     private boolean showUnplayed = false;
@@ -78,6 +80,45 @@ public class FeedItemFilter {
                     break;
             }
         }
+
+        mQuery = makeQuery();
+    }
+
+    private String makeQuery() {
+        // The keys used within this method, but explicitly combined with their table
+        String keyRead = PodDBAdapter.TABLE_NAME_FEED_ITEMS + "." + PodDBAdapter.KEY_READ;
+        String keyPosition = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_POSITION;
+        String keyDownloaded = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_DOWNLOADED;
+        String keyMediaId = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_ID;
+        String keyItemId = PodDBAdapter.TABLE_NAME_FEED_ITEMS + "." + PodDBAdapter.KEY_ID;
+        String keyFeedItem = PodDBAdapter.KEY_FEEDITEM;
+        String tableQueue = PodDBAdapter.TABLE_NAME_QUEUE;
+        String tableFavorites = PodDBAdapter.TABLE_NAME_FAVORITES;
+
+        List<String> statements = new ArrayList<>();
+        if (showPlayed)        statements.add(keyRead + " = 1 ");
+        if (showUnplayed)      statements.add(" NOT " + keyRead + " = 1 "); // Match "New" items (read = -1) as well
+        if (showPaused)        statements.add(" (" + keyPosition + " NOT NULL AND " + keyPosition + " > 0 " + ") ");
+        if (showNotPaused)     statements.add(" (" + keyPosition + " IS NULL OR " + keyPosition + " = 0 " + ") ");
+        if (showQueued)        statements.add(keyItemId + " IN (SELECT " + keyFeedItem + " FROM " + tableQueue + ") ");
+        if (showNotQueued)     statements.add(keyItemId + " NOT IN (SELECT " + keyFeedItem + " FROM " + tableQueue + ") ");
+        if (showDownloaded)    statements.add(keyDownloaded + " = 1 ");
+        if (showNotDownloaded) statements.add(keyDownloaded + " = 0 ");
+        if (showHasMedia)      statements.add(keyMediaId + " NOT NULL ");
+        if (showNoMedia)       statements.add(keyMediaId + " IS NULL ");
+        if (showIsFavorite)    statements.add(keyItemId + " IN (SELECT " + keyFeedItem + " FROM " + tableFavorites + ") ");
+        if (showNotFavorite)   statements.add(keyItemId + " NOT IN (SELECT " + keyFeedItem + " FROM " + tableFavorites + ") ");
+
+        if (statements.isEmpty()) {
+            return "";
+        }
+        StringBuilder query = new StringBuilder(" (" + statements.get(0));
+        for (String r : statements.subList(1, statements.size())) {
+            query.append(" AND ");
+            query.append(r);
+        }
+        query.append(") ");
+        return query.toString();
     }
 
     /**
@@ -125,6 +166,17 @@ public class FeedItemFilter {
         return result;
     }
 
+    /**
+     * Express this filter using an SQL boolean statement that can be inserted into an SQL WHERE clause
+     * to yield output filtered according to the rules of this filter.
+     *
+     * @return An SQL boolean statement that matches the desired items,
+     *         empty string if there is nothing to filter
+     */
+    public String getQuery() {
+        return mQuery;
+    }
+
     public String[] getValues() {
         return mProperties.clone();
     }
diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
index b218a73f9..2ba817b94 100644
--- a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
+++ b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
@@ -17,6 +17,7 @@ import java.util.Map;
 import de.danoeh.antennapod.core.feed.Chapter;
 import de.danoeh.antennapod.core.feed.Feed;
 import de.danoeh.antennapod.core.feed.FeedItem;
+import de.danoeh.antennapod.core.feed.FeedItemFilter;
 import de.danoeh.antennapod.core.feed.FeedMedia;
 import de.danoeh.antennapod.core.feed.FeedPreferences;
 import de.danoeh.antennapod.core.feed.SubscriptionsFilter;
@@ -387,6 +388,30 @@ public final class DBReader {
     }
 
     /**
+     * Loads a filtered list of FeedItems sorted by pubDate in descending order.
+     *
+     * @param offset The first episode that should be loaded.
+     * @param limit The maximum number of episodes that should be loaded.
+     * @param filter The filter describing which episodes to filter out.
+     */
+    @NonNull
+    public static List<FeedItem> getRecentlyPublishedEpisodesFiltered(int offset, int limit,
+                                                                      FeedItemFilter filter) {
+        Log.d(TAG, "getRecentlyPublishedEpisodesFiltered() called with: "
+                + "offset = [" + offset + "]" + " limit = [" + limit + "]" + " filter = [" + filter.getQuery() + "]");
+
+        PodDBAdapter adapter = PodDBAdapter.getInstance();
+        adapter.open();
+        try (Cursor cursor = adapter.getRecentlyPublishedItemsCursorFiltered(offset, limit, filter)) {
+            List<FeedItem> items = extractItemlistFromCursor(adapter, cursor);
+            loadAdditionalFeedItemListData(items);
+            return items;
+        } finally {
+            adapter.close();
+        }
+    }
+
+    /**
      * Loads the playback history from the database. A FeedItem is in the playback history if playback of the correpsonding episode
      * has been completed at least once.
      *
diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
index 539bedd9f..a02cce504 100644
--- a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
+++ b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
@@ -31,6 +31,7 @@ import java.util.Set;
 import de.danoeh.antennapod.core.feed.Chapter;
 import de.danoeh.antennapod.core.feed.Feed;
 import de.danoeh.antennapod.core.feed.FeedItem;
+import de.danoeh.antennapod.core.feed.FeedItemFilter;
 import de.danoeh.antennapod.core.feed.FeedMedia;
 import de.danoeh.antennapod.core.feed.FeedPreferences;
 import de.danoeh.antennapod.core.preferences.UserPreferences;
@@ -116,14 +117,14 @@ public class PodDBAdapter {
     public static final String KEY_FEED_SKIP_ENDING = "feed_skip_ending";
 
     // Table names
-    static final String TABLE_NAME_FEEDS = "Feeds";
-    static final String TABLE_NAME_FEED_ITEMS = "FeedItems";
-    static final String TABLE_NAME_FEED_IMAGES = "FeedImages";
-    static final String TABLE_NAME_FEED_MEDIA = "FeedMedia";
-    static final String TABLE_NAME_DOWNLOAD_LOG = "DownloadLog";
-    static final String TABLE_NAME_QUEUE = "Queue";
-    static final String TABLE_NAME_SIMPLECHAPTERS = "SimpleChapters";
-    static final String TABLE_NAME_FAVORITES = "Favorites";
+    public static final String TABLE_NAME_FEEDS = "Feeds";
+    public static final String TABLE_NAME_FEED_ITEMS = "FeedItems";
+    public static final String TABLE_NAME_FEED_IMAGES = "FeedImages";
+    public static final String TABLE_NAME_FEED_MEDIA = "FeedMedia";
+    public static final String TABLE_NAME_DOWNLOAD_LOG = "DownloadLog";
+    public static final String TABLE_NAME_QUEUE = "Queue";
+    public static final String TABLE_NAME_SIMPLECHAPTERS = "SimpleChapters";
+    public static final String TABLE_NAME_FAVORITES = "Favorites";
 
     // SQL Statements for creating new tables
     private static final String TABLE_PRIMARY_KEY = KEY_ID
@@ -1053,6 +1054,15 @@ public class PodDBAdapter {
         return db.rawQuery(query, null);
     }
 
+    public final Cursor getRecentlyPublishedItemsCursorFiltered(int offset, int limit,
+                                                                FeedItemFilter filter) {
+        String filterQuery = filter.getQuery();
+        String whereClause = "".equals(filterQuery) ? "" : " WHERE " + filter.getQuery();
+        final String query = SELECT_FEED_ITEMS_AND_MEDIA + whereClause
+                + " ORDER BY " + KEY_PUBDATE + " DESC LIMIT " + offset + ", " + limit;
+        return db.rawQuery(query, null);
+    }
+
     public Cursor getDownloadedItemsCursor() {
         final String query = SELECT_FEED_ITEMS_AND_MEDIA
                 + "WHERE " + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOADED + " > 0";