SELECT u1.id, u1.parent_id, 0 as referrals FROM users u1 WHERE

parent_id != 0
UNION
SELECT u.id, u.parent_id, rf.id as referrals
FROM referral rf, users u
WHERE rf.parent_id = u.id
)
select * from referral

о, я сделал такое, но появилась проблема что родитель у которого много детей, появляется в запросе много раз, что логично, UNION решал это, но сейчас как появилось поле referrals, логично что не работает, можно как то этот referrals сгрупировать в массив?

Нет,нету. Просто два поля, Id и parent_Id, любой юзер ссылается на parent_id, если он есть, если нету то parent_id = 0, а что не так в моем коде? Почему вы сказали про моделб

Структуру я хотел сделать такую, что будет id, и его referrals, referrals массив из всех юзеров у которых parent_id = id, а дальше если и у них есть дети, то они будут в выборке тоже как родители с их referrals, по сути выборка которуюя сделал,но referrals сделать массивом и обьединить

Не так то, что она не консистентная — возможны ссылки на несуществующих пользователей (да и пользователя с id = 0 на самом деле не существует). Некачественная модель, короче. ;)

я бы сделал parent_id null, если его нет, но человек решил сделать 0)

И теперь это невозможно исправить?

я не отвечаю вообще за базу, за бек, не в моих полномочиях короче)

Я ничего не понял. :( Вы можете привести текстом адекватный пример данных и результата на этих данных? > но в топ парент находится сам он, с чем это может быть связано? С тем, что я считаю, что он и есть top_parent, и явно это написал в запросе. ;) > то они будут в выборке тоже как родители с их referrals Т.е. это всего один уровень для каждого родителя (тогда зачем тут rCTE)? Или я не так понял?

И получил "прекрасный" побочный эффект — теперь можно ссылаться на несуществующих пользователей. :(

id parent_id 1 0 2 1 3 1 4 1 5 4 6 5 7 4 8 7 результатом будет id referrals 1 [2,3,4] 4 [5,7] 5 [6] 7 [8]

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

Так rCTE тут не нужно, вроде: SELECT u.id, array_agg(child.id ORDER BY child.id) AS referrals FROM users AS u JOIN users AS child ON child.parent_id = u.id WHERE EXISTS ( SELECT 1 FROM users AS ue WHERE ue.parent_id = u.id ) GROUP BY u.id;

вот я о том же, просто понял что рекурсия нужна была бы если эта же структура шла в глубь, но это очень глупо и непрактично, спасибо за выборку!

а зачем where exists?

От предыдущего запроса осталось. ;)

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

Если n константа, то нет

в плане? имеете ввиду что если n будет прокидываться снаружи, то рекурсия не нужна?

Нет, если n - это число всегда постоянная и никогда не меняется, то рекурсия ненужна

В общем случае да. Если n — [небольшая] константа, то можно и "расписать" уровни вручную вместо рекурсии... но зачем, казалось бы? ;)

понял, спасибо, пойду пробовать написать