Возможно ли это сделать в одном запросе MySQL?

#php #mysql

Вопрос:

У меня есть две таблицы:

conversation_participants

 id      | conversation_id | user_id
int(11) |  int(11)        |   int(11)

Пользователи

 id      | username   | ....
int(11) | varchar(20)| ....

Я пытаюсь выбрать всех пользователей, у которых есть определенная строка в их имени пользователя, которые (пока) не являются участниками определенного разговора. В настоящее время у меня есть два SQL-запроса:

 SELECT user_id FROM conversation_participants WHERE conversation_id=?

 SELECT id, username from users WHERE username LIKE '%%%{$_GET['q']}%%' ORDER BY id DESC LIMIT 10

И удалите все результаты из второго запроса, которые имеют тот же user_id, что и в первом запросе. Есть ли какой-либо способ объединить эти два запроса в один?

Заранее благодарю

1. Я действительно надеюсь, что мы должным образом очистим $_GET['q'] его, прежде чем использовать в SQL-запросе…

2. Смотрите ниже, это было просто в тесте. Я использую подготовленные инструкции mysqli в производстве.

Ответ №1:

 SELECT id, username from users 
WHERE username LIKE '%%%{$_GET['q']}%%' 
and username not in 
(
  SELECT user_id FROM conversation_participants 
  WHERE conversation_id=users.conversation_id
)
ORDER BY id DESC LIMIT 10

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

В PHP есть несколько функций для этого. Загляните в mysql_real_escape_string

1. Спасибо! Хотя это было не совсем то, что мне было нужно, это помогло мне определить нужный мне запрос. Вот мой последний (отредактированный) запрос: SELECT id, username FROM users WHERE username LIKE '%%%{$_GET['q']}%%' AND id NOT IN ( SELECT user_id FROM conversation_participants WHERE conversation_id = ? ) ORDER BY id DESC

Ответ №2:

В дополнение к приведенному выше ответу я должен добавить, что вы не должны использовать параметры $ _GET (etc) непосредственно в своем запросе, не проверяя его на наличие SQL-инъекций.

1. Использовался только для тестирования, а не в производстве, как вы можете видеть из моего другого запроса (вопросительный знак) Я использую mysqli с подготовленными операторами.

Ответ №3:

Вы можете сделать это с помощью ЛЕВОГО СОЕДИНЕНИЯ вместе с IS NULL

 SELECT 
    u.id, 
    u.username 
from 
    users u
    LEFT JOIN
         conversation_participants cp 
    ON (u.id=cp.user_id AND cp.conversation_id=?)
WHERE 
    username LIKE '%%%{$_GET['q']}%%' AND
    cp.user_id IS NULL
ORDER BY id DESC LIMIT 10

Я считаю, что это более оптимальное решение, чем использование NOT IN() и подзапроса по соображениям производительности.

Ответ №4:

Вы также можете использовать JOIN, что может быть немного быстрее.

 SELECT u.id, u.username from users u LEFT JOIN conversation_participants c ON c.user_id = u.id WHERE u.username LIKE '%%%{$_GET['q']}%%' AND c.conversation_id != ? ORDER BY u.id DESC LIMIT 10

1. Этот запрос, похоже, не работает для меня, во-первых, я получаю каждого пользователя несколько раз (но ничего такого, что нельзя было бы исправить с помощью DISTINCT), но также он не удаляет пользователей, которые уже являются участниками (в чем и заключалась вся идея).

2. Ты прав. Это не сработало бы, если бы в таблице conversation_participants было несколько записей для пользователя.

Ответ №5:

В дополнение к приведенному выше ответу, вы могли бы удалить свое поле «id» в таблице conversation_participants и определить 2 поля conversation_id и user_id в качестве первичного ключа.