php - 在 PHP 和 MySQL 中使用联结表来分类和包含和排除类别

Question

我正在尝试使用手动分配的类别来分析推文。一切都存储在 MySQL 数据库中。我可以毫无问题地添加和删除推文、类别以及它们之间的关系。

使用 OR 逻辑包括类别按预期工作。如果我想找到分类为“Venezuela”或“Maduro”的推文，我将这两个术语发送到名为 $include 的数组中，并将 $include_logic 设置为 “或”。返回分类在任一类别下的推文。太棒了!

当我尝试使用 AND 逻辑(即分类为 所有 的推文包含术语，例如委内瑞拉 和 Maduro)或当我尝试排除时，问题就开始了类别。

代码如下:

function filter_tweets($db, $user_id, $from_utc, $to_utc, $include = null, $include_logic = null, $exclude = null) {

    $include_sql = '';
    if (isset($include)) {
        $include_sql = 'AND (';
        $logic_op = '';
        foreach ($include as $cat) {
            $include_sql .= "{$logic_op}cats.name = '$cat' ";
            $logic_op = ($include_logic != 'and') ? 'OR ' : 'AND '; # AND doesn't work here
        }
        $include_sql .= ')';
    }
    $exclude_sql = ''; # Nothing I've tried with this works.

    $sql = "
        SELECT DISTINCT tweets.id FROM tweets
            LEFT OUTER JOIN tweets_cats ON tweets.id = tweets_cats.tweet_id
            LEFT OUTER JOIN cats ON tweets_cats.cat_id = cats.id 
        WHERE tweets.user_id = $user_id
            AND created_at
                BETWEEN '{$from_utc->format('Y-m-d H:i:s')}'
                    AND '{$to_utc->format('Y-m-d H:i:s')}'
            $include_sql
            $exclude_sql
        ORDER BY tweets.created_at ASC;";

    return db_fetch_all($db, $sql);   
}

db_fetch_all() 在哪里

function db_fetch_all($con, $sql) {

    if ($result = mysqli_query($con, $sql)) {
        $rows = mysqli_fetch_all($result);
        mysqli_free_result($result);
        return $rows;
    }
    die("Failed: " . mysqli_error($con)); 
}

and tweets_cats 是 tweets 和 cats 表之间的连接表。

在阅读了联接和联结表之后，我明白了为什么我的代码在上述两种情况下不起作用。它一次只能查看一条推文和相应的类别。因此，要求它忽略归类为“X”的推文是没有实际意义的，因为当遇到相同的推文并归类为“Y”时它不会忽略它。

我不明白的是如何修改代码以使其正常工作。我还没有找到任何人试图做类似事情的例子。也许我没有在寻找合适的术语。如果有人能给我指出一个很好的资源，让我在 MySQL 中使用与我使用它们的方式类似的联结表，我将不胜感激。

---

编辑:这是使用上述示例的函数创建的工作 SQL，包括 VP 推特帐户上的“Venezuela”或“Maduro”，日期范围设置为本月到目前为止的推文(EST 转换为 UTC ).

SELECT DISTINCT tweets.id FROM tweets
    LEFT OUTER JOIN tweets_cats ON tweets.id = tweets_cats.tweet_id
    LEFT OUTER JOIN cats ON tweets_cats.cat_id = cats.id
WHERE tweets.user_id = 818910970567344128
    AND created_at BETWEEN '2019-02-01 05:00:00' AND '2019-03-01 05:00:00'
    AND (cats.name = 'Venezuela' OR cats.name = 'Maduro' )
ORDER BY tweets.created_at ASC;

---

更新:这是符合包含类别的 AND 逻辑的工作 SQL。非常感谢@Strawberry 的建议!

SELECT tweets.id FROM tweets
    LEFT OUTER JOIN tweets_cats ON tweets.id = tweets_cats.tweet_id
    LEFT OUTER JOIN cats ON tweets_cats.cat_id = cats.id 
WHERE tweets.user_id = 818910970567344128
    AND created_at BETWEEN '2019-02-01 05:00:00' AND '2019-03-01 05:00:00'
    AND cats.name IN ('Venezuela', 'Maduro')
GROUP BY tweets.id
HAVING COUNT(*) = 2
ORDER BY tweets.created_at ASC;

不过，这有点超出我对 SQL 的理解。我很高兴它有效。我只是希望我了解如何做。

---

更新 2:这是排除类别的有效 SQL。我意识到适用于包含类别的 AND/OR 逻辑也适用于排除类别。此示例使用 OR 逻辑。语法本质上是 Q1 NOT IN (Q2)，其中 Q2 是被排除的内容，这与用于包含的查询基本相同。

SELECT id FROM tweets
WHERE user_id = 818910970567344128
    AND created_at BETWEEN '2019-02-01 05:00:00' AND '2019-03-01 05:00:00'
    AND id NOT IN (
        SELECT tweets.id FROM tweets
            LEFT OUTER JOIN tweets_cats ON tweets.id = tweets_cats.tweet_id
            LEFT OUTER JOIN cats ON tweets_cats.cat_id = cats.id 
        WHERE tweets.user_id = 818910970567344128
            AND created_at BETWEEN '2019-02-01 05:00:00' AND '2019-03-01 05:00:00'
            AND cats.name IN ('Venezuela','Maduro')
    )
ORDER BY created_at ASC;

---

更新 3:这是工作代码。

function filter_tweets($db, $user_id, $from_utc, $to_utc,
                       $include = null, $include_logic = null,
                       $exclude = null, $exclude_logic = null) {

    if (isset($exclude)) {
        $exclude_sql = "
              AND tweets.id NOT IN (\n"
            . include_tweets($user_id, $from_utc, $to_utc, $exclude, $exclude_logic)
            . "\n)";
    } else {
        $exclude_sql = '';
    }
    if (isset($include)) {
        $sql = include_tweets($user_id, $from_utc, $to_utc, $include, $include_logic, $exclude_sql);
    } else {
        $sql = "
            SELECT id FROM tweets
            WHERE user_id = $user_id
              AND created_at
                BETWEEN '{$from_utc->format('Y-m-d H:i:s')}'
                    AND '{$to_utc  ->format('Y-m-d H:i:s')}'
              $exclude_sql";
    }
    $sql .= "\nORDER BY tweets.created_at ASC;";

    return db_fetch_all($db, $sql);   
}

它依赖于这个额外的函数来生成 SQL:

function include_tweets($user_id, $from_utc, $to_utc, $include, $logic, $exclude_sql = '') {

    $group_sql = '';
    $include_sql = 'AND cats.name IN (';
    $comma = '';
    foreach ($include as $cat) {
        $include_sql .= "$comma'$cat'";
        $comma = ',';
    }
    $include_sql .= ')';
    if ($logic == 'and')
        $group_sql = 'GROUP BY tweets.id HAVING COUNT(*) = ' . count($include);
    return "
        SELECT tweets.id FROM tweets
          LEFT OUTER JOIN tweets_cats ON tweets.id = tweets_cats.tweet_id
          LEFT OUTER JOIN cats ON tweets_cats.cat_id = cats.id 
        WHERE tweets.user_id = $user_id
          AND created_at
            BETWEEN '{$from_utc->format('Y-m-d H:i:s')}'
                AND '{$to_utc  ->format('Y-m-d H:i:s')}'
          $include_sql
        $group_sql
        $exclude_sql";
}

Answer 1

执行此操作的一种方法是多次将您的 tweets 表与联结表连接起来，例如像这样:

SELECT tweets.*
FROM tweets
  JOIN tweet_cats AS tweet_cats_foo
    ON tweet_cats_foo.tweet_id = tweets.id
  JOIN tweet_cats AS tweet_cats_bar
    ON tweet_cats_bar.tweet_id = tweets.id
WHERE
  tweet_cats_foo.name = 'foo' AND tweet_cats_bar.name = 'bar'

或者，等价地，像这样:

SELECT tweets.*
FROM tweets
  JOIN tweet_cats AS tweet_cats_foo
    ON tweet_cats_foo.tweet_id = tweets.id
    AND tweet_cats_foo.name = 'foo'
  JOIN tweet_cats AS tweet_cats_bar
    ON tweet_cats_bar.tweet_id = tweets.id
    AND tweet_cats_bar.name = 'bar'

请注意，为简单起见，我在上面假设您的联结表直接包含类别名称。如果您坚持使用数字类别 ID 但按名称搜索类别，我建议创建一个 View ，使用数字类别 ID 将类别和联结表连接在一起，并在查询中使用该 View 而不是实际的联结表。这使您不必为了查找数字类别 ID 而在查询中包含一大堆不必要的样板代码。

对于排除查询，您可以使用 LEFT JOIN 并检查联结表中是否不存在匹配记录(在这种情况下，该表中的所有列都将为 NULL)，像这样:

SELECT tweets.*
FROM tweets
  LEFT JOIN tweet_cats AS tweet_cats_foo
    ON tweet_cats_foo.tweet_id = tweets.id
    AND tweet_cats_foo.name = 'foo'
WHERE
  tweet_cats_foo.tweet_id IS NULL  -- could use any non-null column here

(使用此方法，您确实需要在 LEFT JOIN 子句中包含 tweet_cats_foo.name = 'foo' 条件，而不是 WHERE子句。)

当然，你也可以把这些结合起来。例如，要查找类别 foo 但不在 bar 中的推文，您可以这样做:

SELECT tweets.*
FROM tweets
  JOIN tweet_cats AS tweet_cats_foo
    ON tweet_cats_foo.tweet_id = tweets.id
    AND tweet_cats_foo.name = 'foo'
  LEFT JOIN tweet_cats AS tweet_cats_bar
    ON tweet_cats_bar.tweet_id = tweets.id
    AND tweet_cats_bar.name = 'bar'
WHERE
  tweet_cats_bar.tweet_id IS NULL

或者，同样等价地:

SELECT tweets.*
FROM tweets
  LEFT JOIN tweet_cats AS tweet_cats_foo
    ON tweet_cats_foo.tweet_id = tweets.id
    AND tweet_cats_foo.name = 'foo'
  LEFT JOIN tweet_cats AS tweet_cats_bar
    ON tweet_cats_bar.tweet_id = tweets.id
    AND tweet_cats_bar.name = 'bar'
WHERE
  tweet_cats_foo.tweet_id IS NOT NULL
  AND tweet_cats_bar.tweet_id IS NULL

---

附言。另一种查找类别交叉点的方法，as suggested by Strawberry in the comments above ，是针对联结表执行单个连接，按推文 ID 对结果进行分组，并使用 HAVING 子句来计算为每条推文找到了多少个匹配类别:

SELECT tweets.*
FROM tweets
  JOIN tweet_cats ON tweet_cats.tweet_id = tweets.id
WHERE
   tweet_cats.name IN ('foo', 'bar')
GROUP BY tweets.id
HAVING COUNT(DISTINCT tweet_cats.name) = 2

此方法也可以通过使用第二个(左)连接来推广以处理排除，例如像这样:

SELECT tweets.*
FROM tweets
  JOIN tweet_cats AS tweet_cats_wanted
    ON tweet_cats_wanted.tweet_id = tweets.id
    AND tweet_cats_wanted.name IN ('foo', 'bar')
  LEFT JOIN tweet_cats AS tweet_cats_unwanted
    ON tweet_cats_unwanted.tweet_id = tweets.id
    AND tweet_cats_unwanted.name IN ('baz', 'blorgh', 'xyzzy')
WHERE
  tweet_cats_unwanted.tweet_id IS NULL
GROUP BY tweets.id
HAVING COUNT(DISTINCT tweet_cats_wanted.name) = 2

我没有对这两种方法进行基准测试以确定哪一种更有效，我强烈建议您在决定使用哪一种之前先这样做。原则上，我希望数据库引擎更容易优化多重连接方法，因为它清楚地映射到连接的交集，而对于 GROUP BY ... HAVING 方法一个天真的数据库可能最终会浪费大量的精力首先找到匹配任何类别的所有推文，然后才将 HAVING 子句应用于过滤掉所有匹配所有类别的内容。一个简单的测试用例可以是几个非常大的类别与一个非常小的类别的交集，我期望使用多重连接方法会更有效。但当然，人们应该经常测试这些东西，而不是仅仅依靠直觉。