PHP / MySQL: вопрос таблицы многие-ко-многим / пересечения

Я не совсем уверен, как сформулировать вопрос, поэтому позвольте мне просто привести пример проблемы:

Предположим, есть таблица, в которой элементы сопоставляются с категориями. Каждый элемент может иметь любое количество категорий, и каждая категория, конечно, может содержать любое количество элементов. Итак, у вас есть таблица, которая выглядит так:

items_categories

id item_id category_id

Проблема в том, что я хочу выбрать все идентификаторы элементов, которые имеют идентификаторы определенной категории. Например, выберите все item_id с category_id равным 1 и 2: я хочу найти все элементы, которые связаны с категориями 1 и 2. Очевидно, что я не могу использовать оператор AND, и оператор OR вернет все item_id с любой категорией. , но не обязательно и то, и другое.

Вот мое решение и лучшее, что я могу придумать: выберите все item_ids с category_id равным 1 OR 2; перебирать результаты в PHP и отслеживать, сколько item_ids связано с 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 (как в моем примере выше), мне придется выбрать всю таблицу в этом случае и «вручную» удалить значения 2 и 3 item_id, поскольку они не связаны как с категорией 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" в конце будет заменено количеством категорий, которые я ищу.

Таким образом, он определяет, сколько категорий соответствуют критериям для каждого элемента, и, поскольку мне нужны только те элементы, в которых совпадают ВСЕ категории, он выбирает только те, количество категорий которых равно количеству категорий, которые я ищу. Это, конечно, при условии, что нет повторяющихся категорий или чего-то подобного.

Спасибо за ответы!


mysql php intersect
person user599599    schedule 02.02.2011    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Похоже, что вас беспокоит то, что вы вынуждены выполнять линейный поиск, который, конечно, занимает время O (n), но если вы выбираете элементы из своей базы данных в отсортированном порядке, то не можете ли вы просто использовать двоичный поиск в O (lg n) время?

Надеюсь, это поможет. Если нет, то, возможно, я неправильно понял ваш вопрос и хотел бы, чтобы вы немного его прояснили.

person Jeffrey Greenham    schedule 02.02.2011
comment
Спасибо за ответ. Дело в том, что я не ищу конкретное значение в результатах - я просто хочу знать из своих результатов, со сколькими категориями связан каждый элемент. Если это число отличается от количества категорий, которые я ищу, то я буду знать, что этот элемент не включает все категории. Если я правильно это понимаю, я думаю, что двоичный поиск на самом деле этого не сделает, потому что я не ищу конкретное значение. - person user599599; 02.02.2011
comment
Кстати, я отредактировал свой исходный пост, чтобы, надеюсь, сделать его более понятным. - person user599599; 02.02.2011

arrow_upward
0
arrow_downward 

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

Это то, что вы ищите?

person Brandon Frohbieter    schedule 02.02.2011
comment
Это в основном выполняет то, что я имею в виду, выбирая, где foo = 1 OR foo = 2. Другими словами, это чище, да, но мне пришлось бы выполнить такой же объем обработки массива в PHP, чтобы отфильтровать результаты, в которых foo отсутствует. как 1, так и 2. (Конечно, это невозможно, поскольку для любой данной строки foo имеет одно значение, но в моем случае я ожидаю несколько строк с одним и тем же item_id и набором category_id.) - person user599599; 02.02.2011

arrow_upward
0
arrow_downward

Это то, что вы должны заставить делать базу данных, а не 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 точны, другими словами, что идентификаторы категории и элемента указывают на допустимые записи в таблицах категорий и элементов соответственно. Если вы используете базу данных с поддержкой внешнего ключа (механизм InnoDB для MySQL, Postgres и т. Д.), Принудительное применение внешних ключей несложно.

Чтобы получить список идентификаторов в каждой категории в нужном вам формате, это легко сделать и на стороне 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;

Если вам нужно больше данных, чем просто идентификатор, вы можете присоединиться к таблице, из которой вам нужны данные.

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;
person GordonM    schedule 02.02.2011
comment
Спасибо за ответ - дело в том, что он вернет элементы, которые находятся в любой из категорий, которые вы ему даете, и меня интересуют элементы, которые находятся в всех категорий. В любом случае ваш запрос на подсчет заставил меня задуматься и привел к решению проблемы. Мне просто нужно было подсчитать количество категорий, которым соответствует каждый элемент, и убедиться, что оно равно количеству категорий, которые я ищу. - person user599599; 02.02.2011

arrow_upward

arrow_downward

person    schedule
comment
Это именно то, чего я хочу! Однако когда я запустил его в своей тестовой базе данных (более 1000 элементов), он показался мне немного медленным. Мой последний запрос в исходном посте делает то же самое, но я понял, как сделать это быстрее. - person user599599; 02.02.2011