PHP VideoLibrary_DatabaseHelper::get_select_sql_for_external_videos 예제들

예제 #1

파일: VideoLibrary_RelatedVideosDatabaseHelper.inc.php 프로젝트: saulhoward/haddock-cms

    public static function get_sql_parts_for_external_videos_matching_any_of_these_tags($external_video_library_id, $tag_ids, $external_video_provider_id = NULL, $ignore_video_id = NULL, $start = NULL, $duration = NULL, $options = array('count' => FALSE, 'principal_tags' => FALSE, 'use_ids_for_tags' => TRUE, 'union_query' => FALSE))
    {
        $sql = array();
        if ($options['count']) {
            $sql['select'] = VideoLibrary_DatabaseHelper::get_select_sql_for_external_videos_count(array('union_query' => $options['union_query']));
        } else {
            $sql['select'] = VideoLibrary_DatabaseHelper::get_select_sql_for_external_videos();
            $sql['select'] .= ' ,' . "\n" . 'COUNT( * ) AS tag_count' . "\n";
        }
        $sql['from'] = VideoLibrary_DatabaseHelper::get_from_sql_for_external_videos();
        $sql['from'] .= <<<SQL
,
    hpi_video_library_tags,
    hpi_video_library_tags_to_ext_vid_links
SQL;
        $external_video_library_id = mysql_real_escape_string($external_video_library_id);
        $sql['where'] = <<<SQL
WHERE
    hpi_video_library_external_video_libraries.id 
    = hpi_video_library_ext_vid_to_ext_vid_lib_links.external_video_library_id
AND
    hpi_video_library_external_videos.id 
    = hpi_video_library_ext_vid_to_ext_vid_lib_links.external_video_id

SQL;
        if (isset($external_video_provider_id)) {
            $sql['where'] .= <<<SQL
AND
    hpi_video_library_external_video_providers.id = '{$external_video_provider_id}'

SQL;
        }
        $sql['where'] .= <<<SQL
AND
    hpi_video_library_external_video_libraries.id = '{$external_video_library_id}'
AND
    hpi_video_library_external_videos.external_video_provider_id 
    = hpi_video_library_external_video_providers.id
    AND
    hpi_video_library_external_videos.id = hpi_video_library_tags_to_ext_vid_links.external_video_id
    AND
    hpi_video_library_tags.id = hpi_video_library_tags_to_ext_vid_links.tag_id
    AND
    hpi_video_library_external_videos.status = 'display'
AND
hpi_video_library_external_video_providers.status = 'display'


SQL;
        if (count($tag_ids) > 0) {
            $sql['where'] .= <<<SQL
    AND
(

SQL;
            $i = 0;
            foreach ($tag_ids as $tag_id) {
                $tag_id = mysql_real_escape_string($tag_id);
                if ($i != 0) {
                    $sql['where'] .= <<<SQL
    OR

SQL;
                }
                $i++;
                if ($options['principal_tags']) {
                    if ($options['use_ids_for_tags']) {
                        $sql['where'] .= <<<SQL
    ( hpi_video_library_tags.id = '{$tag_id}' AND hpi_video_library_tags.principal = 'yes' )

SQL;
                    } else {
                        $sql['where'] .= <<<SQL
    ( hpi_video_library_tags.tag = '{$tag_id}' AND hpi_video_library_tags.principal = 'yes' )

SQL;
                    }
                } else {
                    if ($options['use_ids_for_tags']) {
                        $sql['where'] .= <<<SQL
    ( hpi_video_library_tags.id = '{$tag_id}' )

SQL;
                    } else {
                        $sql['where'] .= <<<SQL
    ( hpi_video_library_tags.tag = '{$tag_id}' )

SQL;
                    }
                }
            }
            $sql['where'] .= <<<SQL
)

SQL;
        }
        if ($ignore_video_id > 0) {
            $ignore_video_id = mysql_real_escape_string($ignore_video_id);
            $sql['where'] .= <<<SQL
    AND
    hpi_video_library_external_videos.id <> {$ignore_video_id}

SQL;
        }
        if (!$options['count']) {
            $sql['group_by'] = <<<SQL
GROUP BY
    hpi_video_library_external_videos.id
SQL;
            $sql['limit'] = VideoLibrary_DatabaseHelper::get_limit_sql_for_external_videos($start, $duration);
        }
        return $sql;
    }