Использование соединительных таблиц в PHP и MySQL для категоризации, включения и исключения категорий

Question

Использование соединительных таблиц в PHP и MySQL для категоризации, включения и исключения категорий

Я пытаюсь анализировать твиты, используя назначенные вручную категории. Все хранится в базе данных MySQL. Я могу добавлять и удалять твиты, категории и отношения между ними без каких-либо проблем.

Включение категорий с использованием логики ИЛИ работает, как и ожидалось. Если я хочу найти твиты, отнесенные к категории "Венесуэла" или "Мадуро", я отправляю эти два термина в массив под названием $include с $include_logic установлен в "or", Твиты, отнесенные к любой категории, возвращаются. Большой!

Проблемы начинаются, когда я пытаюсь использовать логику AND (то есть твиты, классифицированные по всем включенным терминам, например, как Венесуэла и Мадуро), или когда я пытаюсь исключить категории.

Вот код:

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)); 
}

а также tweets_cats таблица соединений между tweets а также cats столы.

После прочтения таблиц соединений и соединений я понимаю, почему мой код не работает в двух упомянутых случаях. Он может просматривать только один твит и соответствующую категорию за раз. Поэтому просьба опустить твит, классифицированный как "X", является спорным, потому что он не будет опускать его, когда встречается тот же твит и классифицируется как "Y".

Чего я не понимаю, так это как изменить код, чтобы он работал. Я не нашел примеров людей, пытающихся сделать что-то подобное. Возможно, я не ищу правильные термины. Я был бы признателен, если бы кто-то мог указать мне хороший ресурс для работы с таблицами соединений в MySQL, похожий на то, как я их использую.

Редактировать: Вот рабочий SQL, созданный функцией с использованием вышеупомянутого примера, включая "Венесуэла" ИЛИ "Мадуро" для учетной записи VP в Твиттере с диапазоном дат, установленным на твиты в этом месяце (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;

Обновление: здесь работает SQL, который придерживается логики AND для включенных категорий. Большое спасибо @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, который исключает категории. Я понял, что логика И / ИЛИ, которая применяется к включенным категориям, также относится к исключенным. В этом примере используется логика ИЛИ. Синтаксис, по сути, 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";
}

2

php mysql join mysqli junction-table

Источник

user4806878 10 фев '19 в 00:27

1 ответ

Другие вопросы по тегам php mysql join mysqli junction-table

user411022 10 фев '19 в 05:57 2019-02-10 05:57 · Answer 1 · 2019-02-10 05:57

Один из способов сделать это - присоединиться к 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'

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

Для запросов на исключение вы можете использовать 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

(Используя этот метод, вам нужно включить tweet_cats_foo.name = 'foo' состояние в LEFT JOIN пункт вместо 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

Ps. Другой способ найти пересечения категорий, как предлагает Strawberry в приведенных выше комментариях, состоит в том, чтобы выполнить одно объединение с таблицей соединений, сгруппировать результаты по идентификатору твита и использовать 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 пункт, чтобы отфильтровать все, кроме тех, которые соответствуют всем категориям. Простым тестовым примером для этого может быть пересечение нескольких очень больших категорий с одной очень маленькой, что, как я ожидаю, будет более эффективным при использовании метода множественного объединения. Но, конечно, всегда нужно проверять такие вещи, а не полагаться только на интуицию.