Блог

У меня есть 4 таблицы: posts , users , mentions , following

 posts
----------------------------
id  |  user_id  |  post_text
1      1           foo
2      1           bar
3      2           hello
4      3           jason

users
------------
id  |  name
1      jason
2      nicole
3      frank

mentions
--------------------------
id  |  post_id  |  user_id
1      4           1


following
-------------------------------------------------
id  |  user_id  |  user_id_of_user_being_followed
1      1           2
  

posts включает идентификатор пользователя пользователя, который опубликовал некоторый текст
users , имеет идентификатор пользователя и имя пользователя
mentions , имеет идентификатор сообщения и идентификатор пользователя любого сообщения, в котором упоминается 1 или более других пользователей
following , имеет идентификатор пользователя и пользователя, за которым они следуют (пользователь может следовать от 0 до многих пользователей)

То, что я пытаюсь сделать, это вернуть все сообщения от пользователей a, которым следует данный пользователь, ПЛЮС любые сообщения, в которых упоминается этот пользователь (независимо от того, следит данный пользователь или нет), без возврата каких-либо дубликатов.

 SELECT p.id, p.post, u.name, 
FROM following f 
JOIN posts p ON f.following = p.user_id
JOIN users u ON u.id = p.user_id 
WHERE f.user_id = :user;
  

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

ОБНОВЛЕНИЕ: благодаря Джону Р. я смог это выяснить:

 SELECT DISTINCT(p.id), p.post, u.name 
FROM posts p 
LEFT JOIN following f ON f.following = p.user_id
LEFT JOIN mentions m ON m.posts_id = p.id 
JOIN users u ON u.id = p.user_id
WHERE (f.user_id = :user_id OR m.user_id = :user_id)
  

если я правильно понимаю ваш вопрос, вы бы хотели, чтобы левое соединение включало любые упоминания.. но не отфильтровывать подписчиков / сообщения

если вы можете добавить некоторые образцы данных для воспроизведения, я могу убедиться, что они работают так, как вы этого хотите…

 SELECT 
    if(p.id is not null, p.id, p1.id) as post_id, 
    if(p.post is not null, p.post, p1.post) as post_text, 
    u.username, m.id, m.user_id
FROM posts p
JOIN users u on u.id = p.user_id
JOIN following f on f.user_id_of_user_being_followed = u.id
LEFT JOIN mentions m on m.user_id = f.user_id
LEFT JOIN posts p1 on p1.id = m.post_id
WHERE f.user_id = :user or m.user_id = :user;
  

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

РЕДАКТИРОВАТЬ: РАБОЧАЯ СКРИПКА

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

 (
    SELECT p.id, p.post_text, u.name
    FROM posts p
    JOIN users u on u.id = p.user_id
    JOIN following f on f.user_id_of_user_being_followed = u.id
    WHERE f.user_id = 1
)
UNION
(
    SELECT p.id, p.post_text, u.name
    FROM following f
    JOIN mentions m on m.user_id = f.user_id
    JOIN posts p on p.id = m.post_id
    join users u on u.id = p.user_id
    WHERE f.user_id = 1
);
  

Возможно, вы унаследовали эту базу данных; но последняя таблица на самом деле не соответствует хорошей нормализации данных. В таблице должны быть id и following_id; при настройке у вас в конечном итоге закончатся столбцы (или вам придется продолжать добавлять их, когда пользователь получит сообщение об ошибке) — новые пользователи не смогут ни за кем следить.