PHP/MySQL：多对多/相交表问题

Question

我不太确定如何表达这个问题，所以让我举一个问题的例子：

假设有一个将项目映射到类别的表。每个项目可以有任意数量的类别，每个类别当然可以容纳任意数量的项目。因此，您有一个如下所示的表：

items_categories

id item_id Category_id

问题是，我想选择具有特定类别 id 的所有项目 id。例如，选择类别 id 为 1 和 2 的所有 item_id：我想查找与类别 1 和 2 关联的所有项目。显然，我不能使用 AND 语句，OR 语句将返回任一类别的所有 item_id ，但不一定两者兼而有之。

这是我的解决方案，也是我能想到的最好的办法：选择所有category_id等于1 OR 2的item_ids；迭代 PHP 中的结果并跟踪有多少个 item_id 与一个 Category_id 相关联；然后取消设置结果中不具有指定类别数的所有 item_ids 。这是我的代码片段：

// assume $results is an array of rows from the db
// query: SELECT * FROM items_categories WHERE category_id = 1 OR category_id = 2;
$out = array();
foreach ($results as $result)
{
    if (isset($out[$result['item_id']]))
        $out[$result['item_id']] ++;
    else
        $out[$result['item_id']] = 1;
}
foreach ($out as $key=>$value)
{
    if ($value != 2)
        unset($out($key));
}
return array_keys($out); // returns array of item_ids

显然，如果您有很多不同的类别，那么您选择和处理的信息比理论上需要的信息要多。有什么想法吗？

谢谢！

编辑：这是一个表格示例以及我想要从中获取的信息：

id item_id category_id
1 1 1
2 1 2
3 2 1
4 3 2

假设我有兴趣获取类别 1 和 2 的所有项目。如何从我的列表中获取项目 #1示例表，假设我只想要类别#1 和 #2 的项目？如果我选择类别 1 或 2 的所有内容（如上面的示例），在这种情况下我必须选择整个表并“手动”删除 item_id 的 2 和 3，因为它们不关联包括类别 1 和类别 2。希望这有助于澄清一点。

最终编辑：我想通了，尽管我显然无法描述我想要做什么，呵呵。以下是我提出的查询，供记录：

SELECT *
FROM
(
    SELECT item_id, COUNT(*) as count
        FROM items_categories
        WHERE category_id IN (1, 2)
    GROUP BY item_id
) table_count
WHERE count = 2;

在本例中，“(1, 2)”可以替换为“(category_id1, category_id2, .. .)”，末尾的“2”将替换为我正在搜索的类别数。

因此，它会找出有多少类别与每个项目的条件相匹配，并且由于我只想要所有类别都匹配的项目，因此它只选择类别数量等于我要查找的类别数量的项目。当然，这是假设没有重复的类别或类似的东西。

感谢您的回复！

Answer 1

看来困扰你的是你被迫进行线性搜索，这当然需要 O(n) 时间，但是如果你按排序顺序从数据库中选择元素，那么你不能只使用二分搜索吗O(lg n) 时间？

我希望这有帮助，如果没有，那么也许我误解了你的问题，我希望你能澄清一下。

Answer 2

SELECT
 foo
FROM
 bar
WHERE
foo IN (1,2) 

这是您要找的吗？

Answer 3

这是您应该让数据库而不是 PHP 来做的事情。

SELECT item_id                 # We want a list of item ids
FROM cat_items                 # Gets the item ID list from the cat_items table
WHERE cat_id IN (1, 2, 7, 11)  # List of categories you want to search in
GROUP BY item_id;              # As the same item can appear in more than one category this line will eliminate duplicates

该查询假设 cat_items 中的数据是准确的，换句话说，类别和项目 ID 分别指向类别和项目表中的有效条目。如果您使用的是支持外键的数据库（MySQL、Postgres 等的 InnoDB 引擎），那么执行外键并不困难。

要以您想要的格式获取每个类别中的 ID 列表，这在 SQL 端也可以轻松完成。

SELECT * 
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id, item_id;

如果您只想计算每个类别中有多少个项目，您也可以在 SQL 中执行此操作。

SELECT cat_id, COUNT(item_id) AS items
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id;

如果您需要的数据不仅仅是 ID，那么您可以连接需要从中获取数据的表。

SELECT items.* 
FROM cat_items 
JOIN items ON cat_items.item_id = items.id 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY item_id;

Answer 4

SELECT item_id FROM items_categories WHERE category_id = 1 AND item_id IN (SELECT item_id FROM items_categories WHERE category_id = 2)