Можно ли построить такой запрос на sql?

Question

[lolrofl01]

Привет!
Имеем такую проблему. Есть таблица в БД, в таблице есть записи. Все как обычно, 1 запись - 1 айди. Когда создаешь новую запись, она добавляется в бд, у нее есть специальное поле parent. И при добавлении новой записи поле равно 0. При изменении этой записи на самом деле создается новая, но поле parent теперь равно не нулю, а айди записи, которая была создана в первый раз. Тут суть в том, что в админке отображаются все версии определенной статьи. Какая статья была в самом начале, и как она потом менялась. Отсюда столько записей. Но теперь встала необходимость вывести на сайте эти записи, разумеется, последние их версии. Вот как построить такой запрос, чтобы грубо говоря, выводились все записи у которых parent = 0 (все новые записи), при условии, что у этих статей нет детей (измененных записей, у которых parent = айди этой статьи).
Можно ли такое вообще сделать через запрос?

Answer 1

[Алексей]

id | parent_id

1 | 0
2 | 0
3 | 1 (вот она - первая редакция первой записи)
4 | 2 (первая редакция второй записи)
5 | 0
6 | 1 (вторая редакция первой записи)
7 | 2 (вторая редакция второй записи)

Определите сначала для себя алгоритм
1.Выбрать все записи с parent_id = 0 (это у вас все статьи)
2.Выбрать записи с parent_id = id(из выбрки записей п.1) и максимальным id записи (получите все последние редакции статей)
3.Выбрать записи с parent_id = 0 и id нет в поле parent_id из выборки записей п.2 (статьи не имеющие редакций)
4.Объединить наборы записей п.2 и п.3

Вам осталось только описать этот алгоритм языком SQL

Comments

[ViaCom]

бред

[Алексей]

ViaCom, Почему же бред?
Требую объяснений, сэр!
Неоптимально, да. Но алгоритм задачу решает, значит он имеет место быть.
А вот и его реализация:

SELECT * FROM `t` WHERE `t`.`parent_id` = 0 AND `t`.`id` NOT IN (
    SELECT `t`.`parent_id` FROM `t` WHERE `t`.`id` in (
        SELECT MAX(`t`.`id`) FROM `t` WHERE `t`.`parent_id` in (
                SELECT `t`.`id` FROM `t` WHERE `t`.`parent_id` = 0)
            GROUP BY `t`.`parent_id`
        )
    )
UNION
SELECT * FROM `t` WHERE `t`.`id` in (
    SELECT MAX(`t`.`id`) FROM `t` WHERE `t`.`parent_id` in (
        SELECT `t`.`id` FROM `t` WHERE `t`.`parent_id` = 0)
    GROUP BY `t`.`parent_id`
)

Answer 2

[Lander]

Можно.
Выбирайте все строки со всеми "дочками" с помощью OUTER JOIN, используйте группировку по id и count, а потом WHERE [count] = 1.

upd: Извиняюсь, не WHERE, а HAVING, конечно же.

Comments

[lolrofl01]

Спасибо за ответ! А можно немножечко подробнее с примером?) Что-то я такое набросал, честно говоря не совсем понимаю принципа работы:

SELECT * FROM `table` t1
FULL OUTER JOIN `table` t2
GROUP BY t1.id
HAVING COUNT(1)

Вы ведь правильно поняли, что все это в пределах одной таблицы?

[Lander]

lolrofl01,

Вы ведь правильно поняли, что все это в пределах одной таблицы?

Да. :)
Думаю что у вас это как то так должно выглядеть (перемудрил я чего то с группировкой и having)

SELECT t1.* FROM `table` t1
LEFT OUTER JOIN `table` t2 ON t2.parent_id = t1.id
WHERE t2.id IS NULL

[lolrofl01]

Lander, Еще раз, спасибо!) К сожалению, не до конца верно работает. Почему-то вместо 3 записей (именно столько должно выводиться. У меня 1 запись которую не редактировали вообще, и 2 записи, у которых по 2 редакции есть). Так вот, правильно выводятся эти 3 записи, но у третьей, почему-то, выводятся две редакции, а по идее должна только последняя. Для удобства представлю таблицу:

id | parent_id

1 | 0
2 | 0
3 | 1 (вот она - первая редакция первой записи)
4 | 2 (первая редакция второй записи)
5 | 0
6 | 1 (вторая редакция второй записи)
7 | 2 (вторая редакция второй записи)

То есть, по идее, при сортировке по дате по убыванию, выводиться должны лишь 3 записи, вот с такими айди:
7 , 6 , 5

Первые две - это последние редакции записей с айди 2 и 1 соответственно, и последний айди - это запись без редакций. А получается, что по Вашему запросу выводятся следующие: 7,6,5,4 . Четверка тут явно лишняя)

Есть еще какие-то соображения?))

[Lander]

lolrofl01, А чем концептуально запись 7 отличается от 4? Или, например 3 от 6? Почему именно 7 и 6 должны попадать в выборку, а не, например 3 и 4?

[Lander]

lolrofl01, Под описанный вами пример вполне подходит

SELECT * FROM `table` ORDER BY `id` DESC GROUP BY `parent_id`

[lolrofl01]

Lander,

Почему именно 7 и 6 должны попадать в выборку,

Так дело в том, что 7 и 6 - это последние редакции. То есть, статью изменили во второй раз, ну, скажем, пару предложений добавили, вот и появилась запись с айди 7, она же вторая редакция записи с айди 2. А если еще раз отредактировать эту же запись - будет запись с айди 8, уже третья редакция, тогда вместо 7, должна отображаться 8. В общем всегда только последние редакции, или статьи без редакций (у которых parent_id = 0, и при этом нет других записей с parent_id этой записи (нет детей)), как в примере таблицы, что я выше дал - запись с айди 5.

По поводу запроса - почему-то из-за group by вылезает ошибка:
Неожиданный порядок предложений. (near "ORDER BY" at position 38)

Причем если убрать group by - то все хорошо.

[Lander]

lolrofl01, Ну ладно вам... конец рабочего дня, сами понимаете. Конечно же так:

SELECT * FROM `table` GROUP BY `parent_id` ORDER BY `id` DESC

:)

[Вася]

lolrofl01, group by и order by в другом порядке пишут

[Вася]

Lander, насколько наслышан, это не гарантирует попадание максимального id в выборку
группировка не учтет записи без редакций

[lolrofl01]

Lander, Так в любой последовательности ошибки. Так вылезает ошибка 1055: this is incompatible with sql_mode=only_full_group_by

В общем спасибо за помощь) попробую на php стороне сделать тогда)

[Lander]

Вася, С чего это вдруг не учтёт? Там даже конкретный parent_id стоит - 0. :)

[Вася]

Lander, группировка по paren_id
Тс обозначил, что 0 в этом поле у всех новых записей - склеятся о-О-о-О :)

[Lander]

Вася, Так сам ТС писал:

То есть, по идее, при сортировке по дате по убыванию, выводиться должны лишь 3 записи, вот с такими айди:
7 , 6 , 5

Поэтому я ему и показал запрос, который выведет именно эти записи.

[Вася]

Lander, он не шарит что ему нужно, поэтому и удалил свой ответ :)
По-идее ваш ответ с left outer join будет оптимальным

[Lander]

Вася, У меня тоже была такая похожая задачка. Подумал как будет выглядеть запрос, забил на это дело (потому что записей предполагалось очень много) и в итоге решил её денормализацией root_id прям дополнительным полем в таблицу.

Answer 3

[ViaCom]

Увы я не пишу на мускуле, потому пример для MS SQL
но полагаю с минимальными правками он будет работать и на мускуле.

drop table if exists t1;
create table t1
(Id int primary key, Parent_Id int, code nvarchar(10), TextValue nvarchar(max));
-- Если у стаьи с разной версионностью есть набор полей (или одно поле, у меня это CODE)
-- Которые гарантированно уникальны, тогда все много проще

insert into t1(Id, Parent_Id, code, TextValue) values (1,0, 'Message', 'The variant');
insert into t1(Id, Parent_Id, code, TextValue) values (2,1, 'Message', 'The variant.');
insert into t1(Id, Parent_Id, code, TextValue) values (3,2, 'Message', 'Variant.');
insert into t1(Id, Parent_Id, code, TextValue) values (4,0, 'Letter', 'Hi dear Kat');
insert into t1(Id, Parent_Id, code, TextValue) values (5,4, 'Letter', 'Hi Kat');

-- запрос обычный вложенный
select * from t1 a 
join (select code, max(Parent_Id) Parent_Id from t1 group by code )b 
on a.code = b.code and a.Parent_Id = b.Parent_Id;
-- но если появляется такой "колобок" как описано ниже
-- у нас колобок с Id 6 и колобок с Id 8 это последнии 
-- ревизии разных статей с одним кодом.
insert into t1(Id, Parent_Id, code, TextValue) values (6,0, 'Kolobok', 'K1');
insert into t1(Id, Parent_Id, code, TextValue) values (7,0, 'Kolobok', 'K2');
insert into t1(Id, Parent_Id, code, TextValue) values (8,7, 'Kolobok', 'K3');
-- тогда запрос выйдаст не красивый результат
select * from t1 a 
join (select code, max(Parent_Id) Parent_Id from t1 group by code )b 
on a.code = b.code and a.Parent_Id = b.Parent_Id;

-- нужно делать иерархический запрос, например такой.
with z (Kod, id, parent_id, code, TextValue) as 
(
	select Kod=a.id, a.id, a.parent_id, a.code, a.TextValue from t1 a where parent_id = 0
	union all 
	select z.Kod, a.id, a.parent_id, a.code, a.TextValue from t1 a
	join z on a.Parent_Id = z.id
)
select z.* from z join (select Kod, id = max(id) from z group by Kod ) x on z.Id = x.id;

Comments

[Алексей]

А вот ваш код действительно бред.