public static function top_episode_ids($start, $end = "now", $limit = 3) { global $wpdb; $sql = "\n\t\t\tSELECT\n\t\t\t\tepisode_id, COUNT(*) downloads\n\t\t\tFROM\n\t\t\t\t" . self::table_name() . " di\n\t\t\t\tJOIN " . MediaFile::table_name() . " mf ON mf.id = di.media_file_id\n\t\t\t\tJOIN " . Episode::table_name() . " e ON e.id = mf.episode_id\n\t\t\tWHERE\n\t\t\t\t" . self::sql_condition_from_time_strings($start, $end) . "\n\t\t\tGROUP BY\n\t\t\t\tepisode_id\n\t\t\tORDER BY\n\t\t\t\tdownloads DESC\n\t\t\tLIMIT\n\t\t\t\t0, %d\n\t\t"; return $wpdb->get_col($wpdb->prepare($sql, $limit)); }
/** * Find all post_ids associated with this feed. * * @return array */ function post_ids() { global $wpdb; $allowed_status = array("publish"); $allowed_status = apply_filters("podlove_feed_post_ids_allowed_status", $allowed_status); $sql = "\n\t\t\tSELECT\n\t\t\t\tp.ID\n\t\t\tFROM\n\t\t\t\t" . $wpdb->posts . " p\n\t\t\t\tINNER JOIN " . Episode::table_name() . " e ON e.post_id = p.ID\n\t\t\t\tINNER JOIN " . MediaFile::table_name() . " mf ON mf.`episode_id` = e.id\n\t\t\t\tINNER JOIN " . EpisodeAsset::table_name() . " a ON a.id = mf.`episode_asset_id`\n\t\t\tWHERE\n\t\t\t\ta.id = %d\n\t\t\t\tAND\n\t\t\t\tp.post_status IN (" . implode(',', array_map(function ($s) { return "'{$s}'"; }, $allowed_status)) . ")\n\t\t\tORDER BY\n\t\t\t\tp.post_date DESC\n\t\t"; return $wpdb->get_col($wpdb->prepare($sql, $this->episode_asset()->id)); }
/** * Episodes * * Filter and order episodes with parameters: * * - post_id: one episode matching the given post id * - post_ids: list of episodes matching the given list of post ids * - category: list of episodes matching the category slug * - slug: one episode matching the given slug * - slugs: list of episodes matching the given list of slugs * - post_status: Publication status of the post. Defaults to 'publish' * - order: Designates the ascending or descending order of the 'orderby' parameter. Defaults to 'DESC'. * - 'ASC' - ascending order from lowest to highest values (1, 2, 3; a, b, c). * - 'DESC' - descending order from highest to lowest values (3, 2, 1; c, b, a). * - orderby: Sort retrieved episodes by parameter. Defaults to 'publicationDate'. * - 'publicationDate' - Order by publication date. * - 'recordingDate' - Order by recording date. * - 'title' - Order by title. * - 'slug' - Order by episode slug. * - 'limit' - Limit the number of returned episodes. */ public function episodes($args = []) { return $this->with_blog_scope(function () use($args) { global $wpdb; // fetch single episodes if (isset($args['post_id'])) { return Episode::find_one_by_post_id($args['post_id']); } if (isset($args['slug'])) { return Episode::find_one_by_slug($args['slug']); } // build conditions $where = "1 = 1"; $joins = ""; if (isset($args['post_ids'])) { $ids = array_filter(array_map(function ($n) { return (int) trim($n); }, $args['post_ids'])); if (count($ids)) { $where .= " AND p.ID IN (" . implode(",", $ids) . ")"; } } if (isset($args['slugs'])) { $slugs = array_filter(array_map(function ($n) { return "'" . trim($n) . "'"; }, $args['slugs'])); if (count($slugs)) { $where .= " AND e.slug IN (" . implode(",", $slugs) . ")"; } } if (isset($args['post_status']) && in_array($args['post_status'], get_post_stati())) { $where .= " AND p.post_status = '" . $args['post_status'] . "'"; } else { $where .= " AND p.post_status = 'publish'"; } if (isset($args['category']) && strlen($args['category'])) { $joins .= ' JOIN ' . $wpdb->term_relationships . ' tr ON p.ID = tr.object_id JOIN ' . $wpdb->term_taxonomy . ' tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = "category" JOIN ' . $wpdb->terms . ' t ON t.term_id = tt.term_id AND t.slug = ' . $wpdb->prepare('%s', $args['category']) . ' '; } // order $order_map = array('publicationDate' => 'p.post_date', 'recordingDate' => 'e.recordingDate', 'slug' => 'e.slug', 'title' => 'p.post_title'); if (isset($args['orderby']) && isset($order_map[$args['orderby']])) { $orderby = $order_map[$args['orderby']]; } else { $orderby = $order_map['publicationDate']; } if (isset($args['order'])) { $args['order'] = strtoupper($args['order']); if (in_array($args['order'], array('ASC', 'DESC'))) { $order = $args['order']; } else { $order = 'DESC'; } } else { $order = 'DESC'; } if (isset($args['limit'])) { $limit = ' LIMIT ' . (int) $args['limit']; } else { $limit = ''; } $sql = ' SELECT e.* FROM ' . Episode::table_name() . ' e INNER JOIN ' . $wpdb->posts . ' p ON e.post_id = p.ID ' . $joins . ' WHERE ' . $where . ' AND p.post_type = "podcast" ORDER BY ' . $orderby . ' ' . $order . $limit; $rows = $wpdb->get_results($sql); if (!$rows) { return array(); } $episodes = array(); foreach ($rows as $row) { $episode = new Episode(); $episode->flag_as_not_new(); foreach ($row as $property => $value) { $episode->{$property} = $value; } $episodes[] = $episode; } // filter out invalid episodes return array_filter($episodes, function ($e) { return $e->is_valid(); }); }); }