Архитектура таблицы разделов сайта

[Иван Шумков]

SelenIT
C уровнями все здорово, но вот с title не все так просто:
[sql]
SELECT t4.id, t4.parent_id, t4.level, t4.title, t4.url, t4.handler, t4.source_id, t1.title AS title1, t2.title AS title2, t3.title AS title3 FROM structure t0 LEFT JOIN structure t1 ON (t1.parent_id = t0.id AND t1.url = 'fashion' AND t1.site_spb = 1 AND t1._disabled = 0 AND t1._deleted = 0) LEFT JOIN structure t2 ON (t2.parent_id = t1.id AND t2.url = '234123' AND t2.site_spb = 1 AND t2._disabled = 0 AND t2._deleted = 0) LEFT JOIN structure t3 ON (t3.parent_id = t2.id AND t3.url = '234' AND t3.site_spb = 1 AND t3._disabled = 0 AND t3._deleted = 0) INNER JOIN structure t4 ON t4.id = IF (t3.id IS NOT NULL, t3.id, IF (t2.id IS NOT NULL, t2.id, IF (t1.id IS NOT NULL, t1.id, t0.id))) WHERE t0.parent_id = 0 AND t0.site_spb = 1 AND t0._disabled = 0 AND t0._deleted = 0 AND t0.url = 'impressions'
[/sql]

На выходе получаем:
Array
(
[id] => 4
[parent_id] => 3
[level] => 1
[title] => Мода
=> fashion [handler] => list [source_id] => 0 [title1] => Мода [title2] => [title3] => ) Тоесть t1.title = t4.title Почему?

 

[SelenIT]

Иван Шумков
Поля t4.* (по условию связывания) соответствуют последнему найденному разделу, т.е. последнему из t(i), где t(i).id IS NOT NULL. В вашем примере это оказалась t1. И нужно было выбрать еще t0.title AS title0.

 

[Иван Шумков]

Вот:
[sql]
SELECT t4.id, t4.parent_id, t4.level, t4.title, t4.url, t4.handler, t4.source_id, t0.title AS title0, t1.title AS title1, t2.title AS title2, t3.title AS title3 FROM structure t0 LEFT JOIN structure t1 ON (t1.parent_id = t0.id AND t1.url = 'fashion' AND t1.site_spb = 1 AND t1._disabled = 0 AND t1._deleted = 0) LEFT JOIN structure t2 ON (t2.parent_id = t1.id AND t2.url = 'dsa' AND t2.site_spb = 1 AND t2._disabled = 0 AND t2._deleted = 0) LEFT JOIN structure t3 ON (t3.parent_id = t2.id AND t3.url = 'dsf' AND t3.site_spb = 1 AND t3._disabled = 0 AND t3._deleted = 0) INNER JOIN structure t4 ON t4.id = IF (t3.id IS NOT NULL, t3.id, IF (t2.id IS NOT NULL, t2.id, IF (t1.id IS NOT NULL, t1.id, t0.id))) WHERE t0.parent_id = 0 AND t0.site_spb = 1 AND t0._disabled = 0 AND t0._deleted = 0 AND t0.url = 'impressions'
[/sql]

На выходе:
Array
(
[id] => 4
[parent_id] => 3
[level] => 1
[title] => Мода
=> fashion [handler] => list [source_id] => 0 [title0] => Впечатления [title1] => Мода [title2] => [title3] => ) Попытался сделать условия, чтобы пустые тайтлы не выбирал, но ничего не получилось, конструкция [b]IF и NULLIF[/b] не помогают, получается: [sql] SELECT t4.id, t4.parent_id, t4.level, t4.title, t4.url, t4.handler, t4.source_id, t0.title AS title0, NULLIF(t1.title, NULL) AS title1, NULLIF(t2.title, NULL) AS title2, NULLIF(t3.title, NULL) AS title3 FROM structure t0 LEFT JOIN structure t1 ON (t1.parent_id = t0.id AND t1.url = 'fashion' AND t1.site_spb = 1 AND t1._disabled = 0 AND t1._deleted = 0) LEFT JOIN structure t2 ON (t2.parent_id = t1.id AND t2.url = 'dsa' AND t2.site_spb = 1 AND t2._disabled = 0 AND t2._deleted = 0) LEFT JOIN structure t3 ON (t3.parent_id = t2.id AND t3.url = 'dsf' AND t3.site_spb = 1 AND t3._disabled = 0 AND t3._deleted = 0) INNER JOIN structure t4 ON t4.id = IF (t3.id <> NULL, t3.id, IF (t2.id <> NULL, t2.id, IF (t1.id <> NULL, t1.id, t0.id))) WHERE t0.parent_id = 0 AND t0.site_spb = 1 AND t0._disabled = 0 AND t0._deleted = 0 AND t0.url = 'impressions' [/sql] На выходе: Array ( [id] => 3 [parent_id] => 0 [level] => 0 [title] => Впечатления [url] => impressions [handler] => impressions [source_id] => 0 [title0] => Впечатления [title1] => Мода [title2] => [title3] => )

 

[SelenIT]

А чем мешают пустые тайтлы? При выводе "крошек" будет (level+1) итераций и будет выведено столько же тайтлов, которые по построению не null.

Кстати, второй запрос выдал что-то явно не то. Похоже, <> NULL вместо IS NOT NULL не сработал.

 

[Иван Шумков]

SelenIT
Вы правы!

Странно у меня работает. MySQL 4.1.8-max

-~{}~ 02.08.05 21:53:

SelenIT

Все верно! Вы были правы. С <> NULL не работает.

 

[Rezec]

Раздел, кстати, можно сделать уникальным. Появляется замечательный плюс - при переносе раздела, не меняется его урл. А путь разделов можно показывать и на сайте строкой.
Параметры можно отделить так - site.com/page/separator/a/b/c
или site.com/page/a-b-c.htm.
Такие вот 5 копеек

 

[Иван Шумков]

Rezec
Спасибо. Буду думать.