Как объединить таблицы, чтобы в итоги получить «дерево» вложений?

Question

[AndroidRS]

Обратно я со своим вопросом, пишу api для мобильного приложения. Приложение обычное, наподобие сообщество vk.com. Так вот, пользователь к посту может прикрепить фотографии (от 1 до 6 штук). Соответственно фотографиии загружаются до того момента, когда id поста ещё не известно. Структура таблицы:
attachments - id, path, publication_id (id записи, в момент создание равно 0, обновляется после создания поста)

Таблицы с постами:
wall - id, message

Если выполнить запрос:

SELECT * FROM `wall` LEFT JOIN `attachments` ON `wall`.`id` = `attachments`.`publication_id` ORDER BY `wall`.`id` DESC

То мы получим следующею структуру:

id | message | path | publication_id

4| сообщение | null | null
3 | сообщение | вложение | id записи
3| сообщение | вложение 3| id записи
2 | сообщение | вложение 2 | id записи
2 | сообщение | вложение 2 | id записи
1 | сообщение | вложение 1 | id записи

Т.е если вложений в одной записи два, то ответ будет содержать две строчки, как получить дерево

4| сообщение | null | null
3 | сообщение | вложение 3
------------------- вложение 3

2 | сообщение | вложение 2
-------------------вложение 2

1 | сообщение | вложение 1 | id записи

Comments

[FanatPHP]

что лежит в поле path?

[AndroidRS]

путь к фотографии (url), в таблице есть еще другие поля, но они не влияют на выборку. Все что я хочу получить, это результат наподобие: https://vk.com/dev/wall.get т.е привязать фотографии к публикации и вся проблема в том, что к одной публикации может быть привязано несколько фото. Возможно я выбрал не правильную структуру БД, а может быть мне надо выполнить сортировку в php

[Сергей c0re]

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

так а в чем проблема то??

в этом??

когда id поста ещё не известно.

[AndroidRS]

Сергей c0re, в том чтобы в конечном результате к одной записи привязать несколько объектов из другой таблицы. У одной публикации может быть несколько фотографий, посмотреть результат то что нужно, можете тут в примере ответа сервера https://vk.com/dev/wall.get

P.S Я уже реализовал с помощью php и цикла, только с помощью mysql я не знаю как реализовать

[Сергей c0re]

У одной публикации может быть несколько фотографий, посмотреть результат то что нужно, можете тут в примере ответа сервера https://vk.com/dev/wall.get

вы могли бы указать конкретный результат который нужен вам ? а не посылать на страницу с простыней ?
вы про JSON ??

Т.е. вам нужно получить JSON ??

[Сергей c0re]

AndroidRS,

как получить дерево

в том виде как вы хотите - никак, потому что это табличные данные
либо объясните нормально что вы хотите получить.
JSON можно или точнее частично, т.е. записи, которые потом "заключить" в массив []

[AndroidRS]

Сергей c0re, я уже реализовал такое дерево с помощью php таким алгоритмом:
1. Получаю данные с таблиц wall и attachments и формирую два массива
2. Прохожу по массиву wall и одновременно выполняю поиск вложений по массиву attachments + некоторая оптимизация, например добавил поле wall содержащие количество вложений, соответственно если найдены все вложений или они отсутствуют прекращаю поиск вложений начинаю следующее итерацию
3. Конвертирую массив в json

Не знаю, может быть есть способы и лучше, но пока этот устраивает, а дальше будет видно. По поводу, что мне нужно, если вы бы прочитали, то увидели, что конечный результат, это метод api - как известно почти всегда api возвращает данные в виде json.

[Сергей c0re]

AndroidRS, жутко неоптимальное решение...
и...

если вы бы прочитали, то увидели

если бы вы отвечали, а не упорно посылали читать простыни каких-то непонятных API !
это нужно вам, не мне... ни кому то еще...

и наконец-то вот он ответ, который от вас требовался!!! :

3. Конвертирую массив в json

все это , извиняюсь, извращение:

1. Получаю данные с таблиц wall и attachments и формирую два массива
2. Прохожу по массиву wall и одновременно выполняю поиск вложений по массиву attachments + некоторая оптимизация, например добавил поле wall содержащие количество вложений, соответственно если найдены все вложений или они отсутствуют прекращаю поиск вложений начинаю следующее итерацию

весь этот JSON можно сделать в принципе одним SQL Запросом.

используя джойны, группировку и cancat и group_concat

[AndroidRS]

Сергей c0re, ну покажите мне, если бы я знал как это реализовать я бы не задавал тут вопросы. прочитаю про cancat и group_concat и попробую сам реализовать

[Сергей c0re]

AndroidRS, ну вы свой JSON сюда закиньте хотя бы? и нормальные таблицы.
лучше всего конечно сделать с куском небольших данных на https://dbfiddle.uk/ или sqlfiddle.com

Answer 1

[Алексей Сундуков]

У вас видимо Materialized Path. С ходу нагуглил: Materialized Path в PostgreSQL

Comments

[AndroidRS]

MYSQL

[Алексей Сундуков]

AndroidRS, а это не важно. Я как пример скинул. В остальном искать Materialized Path