Как сделать выборку постов из базы?

Question

[Viktor Grachev]

Доброе утро! Помогите пожалуйста выбрать посты из базы с условием. Уже 2 дня голову ломаю не понимаю как победить запрос) У меня есть 3 таблицы:

Мне необходимо выбрать посты как показано на картинке:

У меня получается сделать выборку всех постов у которых в пивотной таблице есть статус checked без условий. Но необходимо выбрать пост как показано на картинке, например пост с id 39 если у него в столбце cheked стоит статус checked во всех строках. Это означает что все юзеры одобрили пост и его необходимо показать. Подскажите пожалуйста как сделать такое условие в запросе?

Answer 1

[Оптимус Пьян]

В join я не силён не подскажу но где checked и notChecked желательно заменить на 1 и 0 повысится скорость работы.
А в posts нельзя добавить колонку user_id - кучу проблем мне кажется решила бы ))

Comments

[Viktor Grachev]

Колонку добавить можно но она не решит проблему. У меня просто специфика при создании поста назначаются несколько пользователей для его проверки. Их вы можете видеть в пивотной таблице, их бывает разное количество. Поэтому мне никак не записать кучу пользователей назначенных для проверки поста в 1 поле user_id как вы предложили. А насчет замены статуса checked спасибо учту и поменяю все. Главное победить запрос с таким условием.

[Антон Пронин]

Скорость не особо повысится, выигрыш будет только в передаче по сети, если же не планируется разделения базы и движка на разные сервера, то и подавно, только необходимо на поле "сheked" повесить индекс, и заменить тип на SET

[Viktor Grachev]

Антон Пронин: Я вас понял Антон так и сделаю! А можно ли в выборке получить помимо id нужных нам постов также данные самих постов?

[Антон Пронин]

кончено, добавите left join post p on p.id = pu2.post_id

[Антон Пронин]

Ну и SELECT измените SELECT DISTINCT p.*, хотя не очень эффективно,
лучше так

SELECT * FROM posts p WHERE p.id IN(
(SELECT DISTINCT
  pu2.post_id
FROM (SELECT
    t.post_id,
    COUNT(1)
  FROM (SELECT
      pu.post_id,
      cheked
    FROM post_user pu
    GROUP BY pu.post_id,
             pu.cheked) t
  GROUP BY t.post_id
  HAVING COUNT(1) = 1) t2
  LEFT JOIN post_user pu2
    ON pu2.post_id = t2.post_id
  
WHERE pu2.cheked = "checked"))

[Viktor Grachev]

Антон Пронин: Странно при таком запросе возникает ошибка #1054 - Unknown column 'posts.city_id' in 'order clause'

[Антон Пронин]

Viktor Grachev: Скиньте свой запрос, у меня такой ошибке нет

[Viktor Grachev]

Делаю запрос как вы указали.

SELECT * FROM posts p WHERE p.id IN(
(SELECT DISTINCT
pu2.post_id
FROM (SELECT
t.post_id,
COUNT(1)
FROM (SELECT
pu.post_id,
cheked
FROM post_user pu
GROUP BY pu.post_id,
pu.cheked) t
GROUP BY t.post_id
HAVING COUNT(1) = 1) t2
LEFT JOIN post_user pu2
ON pu2.post_id = t2.post_id

WHERE pu2.cheked = "checked"))

https://drive.google.com/file/d/0B8tEf6QRhrDMc09Dc...

[Антон Пронин]

Viktor Grachev: Вы же сейчас работает на локальном ПК, поставьте себе нормальную IDE хотя бы www.devart.com/ru/dbforge/mysql/studio, она бесплатная. а функционал огромный.

[Антон Пронин]

попробуйте из нее, я думаю что то phpMyAdmin чудит

[Viktor Grachev]

Антон Пронин: Да использую на локальном компе базу. Сейчас как раз качаю эту программу, на днях немного попробовал ее и был шокирован ее функционалом! В ней можно создавать реально сложные запросы!!! Спасибо вам еще раз за помощь и поддержку!!!

[Антон Пронин]

Viktor Grachev: Да незачто)

Answer 2

[Антон Пронин]

select * from posts p
left join post_user us
 on us.post_id = p.id
left join users u
 on us.user_id = u.id
where us.checked="checked"

Comments

[Viktor Grachev]

При таком запросе пишет ошибку: #1054 - Unknown column 'us.checked' in 'where clause'

[Антон Пронин]

Понятно, это у Вас таблице ошибка в слове, "Cheked" либо исправьте запрос
на
where us.cheked="checked"
либо таблицу

[Viktor Grachev]

Антон Пронин: Да вы правы извините совсем не заметил опечатку)) Теперь идет выборка постов везде где есть статус checked. И у меня выводится 12 постов)) а необходимо вывести 3 поста как указано на картинке, я отметил их красными рамками. Как сейчас выводится у меня тоже так получилось сделать. Но как сделать условия чтобы выбрал посты отмеченные рамками ума не приложу.

[Антон Пронин]

Ах, не прочитал, последнее ваше предложение в посте, а можете сделать дамп с таблиц строк на 100 что бы поиграться?

Ну а на вскидку, будет так

select * from ( select us.post_id,count(1) c from posts p
 left join post_user us
 on p.id = us.post_id
 group by us.checked,p.post_id ) t
left join post_user us
 on us.post_id = t.post_id
left join users u
 on us.user_id = u.id
where t.c=1

[Viktor Grachev]

Антон Пронин: Да конечно база тестовая вот ссылочка) https://drive.google.com/file/d/0B8tEf6QRhrDMZjZ2d...

[Антон Пронин]

Вот держите

SELECT DISTINCT
  pu2.post_id
FROM (SELECT
    t.post_id,
    COUNT(1)
  FROM (SELECT
      pu.post_id,
      cheked
    FROM post_user pu
    GROUP BY pu.post_id,
             pu.cheked) t
  GROUP BY t.post_id
  HAVING COUNT(1) = 1) t2
  LEFT JOIN post_user pu2
    ON pu2.post_id = t2.post_id
WHERE pu2.cheked = "checked"

[Viktor Grachev]

Антон Пронин: Вы просто гуру)) такой запрос соорудили просто класс!!! Спасибо вам огромное!!! Буду сейчас выводить посты)) Как вы такие сложные запросы делаете если не секрет? Или у вас огромный опыт в работе с базами данных?

[Антон Пронин]

Ну опыт работы с СУБД у меня наверно лет 7-9, точно уже не помню, а что бы такие запросы строить просто нужно думать как СУБД))))
вот смотрите,
первый запрос

SELECT
      pu.post_id,
      cheked
    FROM post_user pu
    GROUP BY pu.post_id,
             pu.cheked

мы его сгрупировали по посту и типу,
нам база вернет список постов, и сольет одинаковые типы в одну строку.
было 300 записей стало только 33,
обернув это еще в один запрос, выбрали только те посты котрые имеют одинаковые типа о чем нам скажет COUNT == 1
ну а далее просто выбираем нужный тип в данном случае только "checked"

[Viktor Grachev]

У вас и правда огромный опыт в работе с БД. Я сейчас как раз видео-курс смотрю по базам данных. Как раз недавно начали всплывать трудности при проектировании сложных баз данных. Когда например делаю приложение сложнее блога начинаются проблемы как организовать структуру БД)) Вот тут сказывается недостаток опыта и поэтому решил изучить этот вопрос более подробно)