public static function get_related_episodes($episode_id = FALSE)
{
global $wpdb;
if (!$episode_id) {
return array();
}
$sql = sprintf('SELECT
*
FROM
' . Episode::table_name() . ' e
WHERE id IN (
SELECT right_episode_id FROM ' . self::table_name() . ' WHERE left_episode_id = %1$d
UNION
SELECT left_episode_id FROM ' . self::table_name() . ' WHERE right_episode_id = %1$d
)', $episode_id);
return Episode::find_all_by_sql($sql);
}
public static function latest()
{
global $wpdb;
// Why do we fetch 10 instead of just 1?
// Because some of the newest ones might be drafts or otherwise invalid.
// So we grab a bunch, filter by validity and then return the first one.
$sql = '
SELECT
*
FROM
`' . Episode::table_name() . '` e
JOIN `' . $wpdb->posts . '` p ON e.post_id = p.ID
ORDER BY
p.post_date DESC
LIMIT 0, 10';
$episodes = array_filter(Episode::find_all_by_sql($sql), function ($e) {
return $e->is_valid();
});
return reset($episodes);
}
/**
* Get episodes related to the given episode.
*
* @param int|bool $episode_id
* @param array $args List of optional arguments.
* only_published - If true, only return already published episodes. Default: false.
* @return array
*/
public static function get_related_episodes($episode_id = false, $args = [])
{
global $wpdb;
$defaults = ['only_published' => false];
$args = wp_parse_args($args, $defaults);
if (!$episode_id) {
return [];
}
$join = '';
if ($args['only_published']) {
$join = 'INNER JOIN ' . $wpdb->posts . ' p ON p.ID = e.post_id AND p.post_status IN (\'publish\', \'private\')';
}
$sql = sprintf('SELECT
e.*
FROM
' . Episode::table_name() . ' e
' . $join . '
WHERE e.id IN (
SELECT right_episode_id FROM ' . self::table_name() . ' WHERE left_episode_id = %1$d
UNION
SELECT left_episode_id FROM ' . self::table_name() . ' WHERE right_episode_id = %1$d
)', $episode_id);
return Episode::find_all_by_sql($sql);
}
public function episodes()
{
global $wpdb;
$prev = $this->previous_season();
$next = $this->next_season();
if (is_null($prev) && is_null($next)) {
// first and only season
$date_range = '1 = 1';
} elseif (is_null($prev)) {
// first, completed season
$date_range = "DATE(p.post_date) < '" . $next->start_date('Y-m-d') . "'";
} elseif (is_null($next)) {
// current running season
$date_range = "DATE(p.post_date) > '" . $prev->end_date('Y-m-d') . "'";
} else {
// anything inbetween
$date_range = "DATE(p.post_date) >= '" . $this->start_date('Y-m-d') . "' AND DATE(p.post_date) <= '" . $this->end_date('Y-m-d') . "'";
}
$sql = "SELECT\n\t\t\t\te.*\n\t\t\tFROM\n\t\t\t\t`" . Episode::table_name() . "` e\n\t\t\t\tJOIN `" . $wpdb->posts . "` p ON e.post_id = p.ID\n\t\t\tWHERE\n\t\t\t\tp.post_type = 'podcast' AND\n\t\t\t\tp.post_status = 'publish' AND\n\t\t\t\t{$date_range}\n\t\t\tORDER BY\n\t\t\t\tp.post_date ASC\n\t\t";
return Episode::find_all_by_sql($sql);
}
