php - mySQL 连接三个表并搜索在一个表中找到的多个关键字

Question

我试图通过避免包含相同单词的多个记录来限制数据库的大小，因此我构建了两个表，用于将关键字与其他表中的行相关联。

作为示例，我们使用以下搜索输入:“查找我的”

希望得到的点击是来自product_id = 106 的product_name。我可以只搜索产品名称吗？不可以，因为产品可以与不属于产品名称一部分的其他关键字相关联。

表:ec_products

product_id    | product_name    | is_deleted
---------------------------------------------
106           | some product    | 0

表:ec_keywords_index

word_id       | keyword
---------------------------------------------
55            | find
61            | my
77            | product

表:ec_keyword_relations

word_id       | application_id    | related_id
------------------------------------------------
55            | 1                 | 106
61            | 1                 | 106
77            | 1                 | 106

所以，当我在搜索输入中输入“查找我的”时，我现在想要的是:

• 我只想要 ec_keyword_relations.application_id = 1 的点击
• 我的搜索字符串中的所有单词都应该与产品相关。例如。如果我将搜索输入更改为“查找我的裤子”，我们不应该找到product_id = 106。

这是我到目前为止所尝试过的(我知道目前我距离可行的解决方案还很远):

    $searchInput = 'find my';
    $keywords = explode(' ', $searchInput);

    $keyword_count = count($keywords);

    $n = 1;
    $query = '';

    foreach($keywords as $keyword) { 
        if ($n !== $keyword_count) { 
            $query_ext = ' AND ';
        } else { 
            $query_ext = ''; 
        } 

        $query .= "(ec_keywords_index.keyword LIKE '%$keyword%')" . $query_ext;

        ++$n;
    }

    $query = "
        SELECT 
            ec_products.product_name

        FROM 
            ec_products

        LEFT JOIN ec_search_word_relations
            ON ec_keyword_relations.related_id = ec_products.product_id

        LEFT JOIN ec_keywords_index
            ON ec_keywords_index.word_id = ec_keyword_relations.word_id

        WHERE 
            ($query) 
        AND 
            ec_products.is_deleted = '0' 
        AND 
            ec_keyword_relations.application_id = '1'

        GROUP BY
            ec_products.product_id

        ORDER BY 
            ec_products.product_name ASC 

        LIMIT 
            100";

    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($name);
    while ($stmt->fetch()) {
        echo $name;
    }

更新:

经过一番摆弄后，我想出了这个解决方案，它的工作原理与预期的一样。 (就性能而言)这是一个好还是坏的解决方案？

    $searchInput = 'find my';
    $keywords = explode(' ', $searchInput);

    $keyword_count = count($keywords);

    $n = 1;
    $where_query = '';
    $having_query = '';

    foreach($keywords as $keyword) { 
        if ($n != $keyword_count) {
            $w_query_ext = ' OR '; 
            $h_query_ext = ' AND ';
        } else { 
            $w_query_ext = ''; 
            $h_query_ext = ''; 
        }

        $where_query .= "ec_keywords_index.keyword LIKE '%" . $keyword . "%'" . $w_query_ext;

        $having_query .= "related_keywords LIKE '%" . $keyword . "%'" . $h_query_ext;

        ++$n;
    }

    $query = "
        SELECT 
            ec_products.product_name, 
            GROUP_CONCAT(' ', ec_keywords_index.keyword) AS 'related_keywords'

        FROM 
            ec_products, 
            ec_keywords_index

        LEFT JOIN 
            ec_keyword_relations 
            ON ec_keyword_relations.word_id = ec_keywords_index.word_id

        WHERE ($where_query) 
            AND ec_products.product_id = ec_keyword_relations.related_id 
            AND ec_keyword_relations.application_id = '1' 
            AND ec_products.is_deleted = '0'

        GROUP BY 
            ec_products.product_name

        HAVING 
            ($having_query)

        LIMIT 
            100";

    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($name);
    while ($stmt->fetch()) {
        echo $name;
    }

上面的示例输出如下查询:

    SELECT 
        ec_products.product_name, 
        GROUP_CONCAT(' ', ec_keywords_index.keyword) AS 'related_keywords'

    FROM 
        ec_products, 
        ec_keywords_index

    LEFT JOIN 
        ec_keyword_relations 
        ON ec_keyword_relations.word_id = ec_keywords_index.word_id

    WHERE (ec_keywords_index.keyword LIKE '%find%' OR ec_keywords_index.keyword LIKE '%my%') 
        AND ec_products.product_id = ec_keyword_relations.related_id 
        AND ec_keyword_relations.application_id = '1' 
        AND ec_products.is_deleted = '0'

    GROUP BY 
        ec_products.product_name

    HAVING 
        (related_keywords LIKE '%find%' AND related_keywords LIKE '%my%')

    LIMIT 
        100

我使用 GROUP_CONCAT 创建一个包含与产品相关的所有关键字的列，然后我的 WHERE 子句用于仅选择在我的搜索字符串中实际找到的关键字...然后 HAVING 子句用于使用 GROUP_CONCAT 在列构建中搜索。

Answer 1

自己的答案和示例:

经过一番摆弄后，我想出了这个解决方案，它的工作原理与预期的一样。我不知道这是否是最佳实践，但它确实很有魅力。

    $searchInput = 'find my';
    $keywords = explode(' ', $searchInput);

    $keyword_count = count($keywords);

    $n = 1;
    $where_query = '';
    $having_query = '';

    foreach($keywords as $keyword) { 
        if ($n != $keyword_count) {
            $w_query_ext = ' OR '; 
            $h_query_ext = ' AND ';
        } else { 
            $w_query_ext = ''; 
            $h_query_ext = ''; 
        }

        $where_query .= "ec_keywords_index.keyword LIKE '%" . $keyword . "%'" . $w_query_ext;

        $having_query .= "related_keywords LIKE '%" . $keyword . "%'" . $h_query_ext;

        ++$n;
    }

    $query = "
        SELECT 
            ec_products.product_name, 
            GROUP_CONCAT(' ', ec_keywords_index.keyword) AS 'related_keywords'

        FROM 
            ec_products, 
            ec_keywords_index

        LEFT JOIN 
            ec_keyword_relations 
            ON ec_keyword_relations.word_id = ec_keywords_index.word_id

        WHERE ($where_query) 
            AND ec_products.product_id = ec_keyword_relations.related_id 
            AND ec_keyword_relations.application_id = '1' 
            AND ec_products.is_deleted = '0'

        GROUP BY 
            ec_products.product_name

        HAVING 
            ($having_query)

        LIMIT 
            100";

    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($name);
    while ($stmt->fetch()) {
        echo $name;
    }

上面的示例输出如下查询:

    SELECT 
        ec_products.product_name, 
        GROUP_CONCAT(' ', ec_keywords_index.keyword) AS 'related_keywords'

    FROM 
        ec_products, 
        ec_keywords_index

    LEFT JOIN 
        ec_keyword_relations 
        ON ec_keyword_relations.word_id = ec_keywords_index.word_id

    WHERE (ec_keywords_index.keyword LIKE '%find%' OR ec_keywords_index.keyword LIKE '%my%') 
        AND ec_products.product_id = ec_keyword_relations.related_id 
        AND ec_keyword_relations.application_id = '1' 
        AND ec_products.is_deleted = '0'

    GROUP BY 
        ec_products.product_name

    HAVING 
        (related_keywords LIKE '%find%' AND related_keywords LIKE '%my%')

    LIMIT 
        100

我使用 GROUP_CONCAT 创建一个包含与产品相关的所有关键字的列，然后我的 WHERE 子句用于仅选择在我的搜索字符串中实际找到的关键字...然后 HAVING 子句用于使用 GROUP_CONCAT 在列构建中搜索。