php - 在此查询中添加多对多表关系

Question

我有一个相对复杂的查询(以我有限的 MYSQL 知识):

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    song_description, uploaded_time, emotion, tempo,
    user, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count, waveform, datasize, display_name, user_url,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted 
FROM (
        SELECT
            sp.songsID, projects_count,
            AVG(rating) as rating,
            COUNT(rating) AS ratings_count,
            COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count

                FROM (
                    SELECT songsID, COUNT(*) as projects_count
                    FROM $sTable s
                    LEFT JOIN $sTable2 p ON s.songsID = p.songs_id

                    GROUP BY songsID) as sp

            LEFT JOIN $sTable3 r ON sp.songsID = r.songid

            GROUP BY sp.songsID) as spr


JOIN $sTable s USING (songsID)
LEFT JOIN $sTable5 q ON s.user = q.ID   


        $sWhere
        $sOrder
        $sLimit

此查询从 5 个不同的表中获取有关给定歌曲的所有信息。

变量 $sWhere 构建查询的 WHERE 部分，并依赖于发送到服务器的数据，以便根据用户输入动态过滤此歌曲列表。

$sOrder 和 $sLimit 根据用户输入再次对结果进行排序和限制。

我现在需要在这个等式中添加第 6 个表，其中包含流派列表 (wp_genres)，此表通过具有以下结构的链接表 (wp_song_genres) 链接到 $sTable 中的 songsID 列:-

$sTable(wp_songs)    $sTable6(wp_genre_songs)     $sTable7(wp_genres)
songsID*             song_id*                     genre_id**
column2              genre_id**                   genre_name
column3                                           icon_url
column4
column5
etc...

星号表示链接。

我需要做的是让我的歌曲结果动态过滤器能够按流派过滤歌曲列表，但我不知道如何引入新表($sTable6 和 $ sTable7) 进入查询。

下面是生成查询的 WHERE 部分的代码:

这部分获取通过“sSearch”收到的文本并在列中搜索任何匹配的文本。

    $sWhere = "";
if ( $_GET['sSearch'] != "" )
{
    $aWords = preg_split('/\s+/', $_GET['sSearch']);
    $sWhere = "WHERE (";

    for ( $j=0 ; $j<count($aWords) ; $j++ )
    {
        if ( $aWords[$j] != "" )
        {
            $sWhere .= "(";
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $aWords[$j] )."%' OR ";
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ") AND ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -4 );
    $sWhere .= ')';
}

这部分是一个“范围”过滤器，因此用户可以输入最小速度值和最大速度值(通过字体末端的 jquery 范围 slider 完成)。数据通过 sSearch_* 接收。

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }







        $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
        // check for values range
        $rangeSeparator = "~";
        if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
            // get min and max

             $columnFilterRangeMatches =  explode('~', $columnFilterValue);

            // get filter
            if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                $sWhere .= " 0 = 0 ";
            else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
            else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
            else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
        } else {
            $sWhere .= $aColumns[$i] . " LIKE '%" . $columnFilterValue . "%' ";
        }
    }
}

genre_id 的数据将通过 sSearch_23 传入。即 $_GET['sSearch'] 将包含我希望过滤结果的 genre_id。请注意，我需要能够将许多流派标签的结果加在一起。

如果用户选择 genre:rock (genre_id: 1)，则 $sTable 中所有具有 genre_id:1 关联的歌曲都应该返回。 (以及我当前查询检索到的有关歌曲的其余信息。

但是...如果用户选择了 genre:rock (genre_id:1) AND genre:pop (genre_id:2) AND genenre:rap (genre_id:3) 那么所有带有任何这些标签的歌曲都应该被返回。

很抱歉，这是一个如此复杂的问题，我意识到我可能没有表达清楚。我也明白这完全是针对我当前的代码的。不管怎样，我真的希望有人能帮助我，或者至少让我朝着正确的方向前进。

非常感谢!

编辑:

我刚刚通过将以下内容添加到我的查询中来设法让他“接近”工作:

LEFT JOIN (
            SELECT igs.song_id, igs.genre_id, g.genre_name
            FROM $sTable6 AS igs
            JOIN wp_genres AS g 
            ON igs.genre_id = g.genre_id
            ) as gs 
            ON songsID = gs.song_id         

所以整个事情现在看起来像:-

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    genre, song_description, uploaded_time, emotion, tempo,
    user, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count, waveform, datasize, display_name, user_url, genre_id,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted 
FROM (
        SELECT  
            sp.songsID, projects_count, 
            AVG(rating) as rating,
            COUNT(rating) AS ratings_count,
            COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count

                FROM (
                    SELECT songsID, COUNT(*) as projects_count
                    FROM $sTable s
                    LEFT JOIN $sTable2 p ON s.songsID = p.songs_id

                    GROUP BY songsID) as sp

            LEFT JOIN $sTable3 r ON sp.songsID = r.songid





            GROUP BY sp.songsID) as spr

LEFT JOIN (
            SELECT igs.song_id, igs.genre_id, g.genre_name
            FROM $sTable6 AS igs
            JOIN wp_genres AS g 
            ON igs.genre_id = g.genre_id
            ) as gs 
            ON songsID = gs.song_id         



JOIN $sTable s USING (songsID)
LEFT JOIN $sTable5 q ON s.user = q.ID   





        $sWhere
        $sOrder
        $sLimit

按 genre_id 的查询现在可以按预期完美动态地进行。但是，当在查询的 WHERE 部分中未指定 genre_id 时，我得到了重复的行。在歌曲/流派链接表中有条目的每首歌曲都会被复制。

首先，就优化/最佳实践而言，我当前的解决方案是否是一个好的解决方案？其次，如何防止这些重复行？

注意:我在链接表的两列中已经有了一个唯一的主键。

Answer 1

http://hackmysql.com/case4我认为这对您来说是一本很好的读物，尤其是考虑到您的数据库可能会达到大量行。在 mysql 表中使用索引键可以大大加快查询速度，其中一个表必须从另一个表中提取，或者一个表有许多行，这些行可能在一列中有相似的值，但在其他列中没有。这使他们不必扫描整个表，至少据我所知。