Использование соединительных таблиц в 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";
}

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'

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

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

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