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();
});
});
}
