SQLite и древовидный запрос

@CoolMind · Регистрация: 06.10.2012

Author24 — интернет-сервис помощи студентам

Привет!
Создаю в SQLite browser древовидный запрос, вот его часть.

SQL

SELECT *
FROM rubric
WHERE id IN (
WITH tree (id) AS 
(
  SELECT id
  FROM rubric
  WHERE parent_id = 137 
 
  UNION ALL
 
  SELECT r2.id
  FROM rubric r2
  INNER JOIN tree ON tree.id = r2.parent_id
)
SELECT id
FROM tree)

Всё отрабатывает. Загоняю весь запрос в программу, запускаю и получаю следующее сообщение:

Java

1 2	FATAL EXCEPTION: main android.database.sqlite.SQLiteException: near "tree": syntax error (code 1): , while compiling: SELECT .........

Означает ли это, что Андроидная SQLite не так хороша, как PC-шная?
Пока создавал тему, увидел вот такую: Создать "древовидный" список, но там, вроде, проблема тоже не решена.

@dubok79 · 31.10.2014, 21:57

Никогда не видел такого запроса, возможно это фича именно SQLite. В каком виде у вас таблица? Обычно когда строят дерево то строится одна ветвь и подгружается дочерняя. При разворачивании процесс повторяется. Или вам надо как по ссылке чисто показать результат в виде дерева?

Добавлено через 6 минут
Не знаю как в SQLite одним запросом сделать. В ней нет хранимых процедур, но можно обойти это кодом в самом проекте. Т.е. результат формировать в некую временную табличку, а потом ее вывести. Можно рекурсивно, а можно по цепочке опускаться вниз, пока не найдете самую длинную ветку. Мы на MSSQL обычно так делаем.

@CoolMind · 31.10.2014, 22:32 **[ТС]**

dubok79, спасибо за ответ. Я не так часто обращаюсь нынче к СУБД, раньше чаще. Конечно же, возможность создания рекурсивных запросов есть и в Oracle, и в SQL Server, а теперь есть и в SQLite. Но, как обычно, до Андроида прогресс ещё не дошёл. Хорошо хоть для Андроида есть нормальные IDE типа Android Studio и IntelliJ IDEA, а то было бы совсем тяжко. Недавно пробовал делать импорт базы при помощи Java, скачал NetBeans, так после Android Studio показалась поделкой проще Eclipse. Ещё и библиотеки пришлось скачивать. Да, что-то инструментарий слабоват.
По теме запроса я пока решил так, что, поскольку уровень вложенности не превышает 2, сделал несколько вложенных запросов. Мне надо просто единым списком показать все позиции (без сворачивания-разворачивания узлов). Соответственно, тема эта была создана для того, чтобы люди обратили внимание на слабость инструментария.

Добавлено через 8 минут
К сожалению, пока не могу найти ссылку на stackoverflow, где я взял пример запроса. Вот описание от SQLite: http://www.sqlite.org/lang_with.html, там же интересные примеры.

@dubok79 · 31.10.2014, 22:43

Еще я такие вещи делаю по полю HistoryID. В нем хранится весь путь где лежит позиция. Формировать его проще простого. Делать поиск еще проще. Например выглядит так: 1.23.4. Где 1 это ид самой верхней папки, 23 ид родительской папки, а 4 собственная идешка.

@CoolMind · 31.10.2014, 22:50 **[ТС]**

dubok79, да, знакомая тема. Согласен, в сложных (или медленных) запросах иногда полезно хранить избыточные данные, дополнительные поля. Проблема только в том, чтобы эти данные всегда были правильными, а это значит, что надо периодически их обновлять (или делать триггеры на обновление).

Добавлено через 2 минуты
Может возникнуть вопрос, а что же здесь обновлять? Ситуации бывают разные, например, перенос вершины в другое место (переподчинение) или изменение порядка следования вершин в одном поддереве.

@dubok79 · 31.10.2014, 22:55

Сообщение от CoolMind

Может возникнуть вопрос, а что же здесь обновлять? Ситуации бывают разные, например, перенос вершины в другое место (переподчинение) или изменение порядка следования вершин в одном поддереве.

Ну это все конечно же надо описывать в своем коде. Конечно же я думаю всегда про большие таблички

Такая уж у меня работа.

@CoolMind · 21.11.2014, 11:30 **[ТС]**

dubok79, спасибо за напоминание о дополнительных полях. Возьму на вооружение при случае. В других проектах раньше тоже так делал, затем использовал LIKE для отбора нужных вершин.
Вчера делал снова запрос, но он очень долго выполнялся (из-за вложенностей), и я решил переписать. Открыл пару сайтов: http://habrahabr.ru/post/43955/ и http://www.sql.ru/forum/104183... -al-derevo, вспомнил, что при известном максимальном уровне вложенности можно обойтись без рекурсии (как во втором сайте). Получается быстро.

@dubok79 · 21.11.2014, 13:11

Рекурсия это вообще хорошо, но плохо. Долго работает, как впрочем и курсоры.

@CoolMind · 21.11.2014, 15:15 **[ТС]**

dubok79, оказывается exists - довольно хороший оператор.
Целый день мучался, хотел вывести только те вершины первого уровня, которые содержат дочерние вершины, связанные с другой таблицей. Ну, т.е. отбросить на первом уровне все вершины, у которых дочерние с другой таблицей не связаны. Запрос выполнялся десяток секунд, пока не применил exists. После этого время уменьшилось до десятков миллисекунд.
Стало примерно так:

SQL

SELECT DISTINCT r.*
FROM
  (SELECT r1.*, r2.id AS id2, r3.id AS id3
  FROM rubric r1
    LEFT JOIN rubric r2 ON r2.parent_id = r1.id
    LEFT JOIN rubric r3 ON r3.parent_id = r2.id
  WHERE r1.parent_id IS NULL
  ) r
WHERE EXISTS 
  (SELECT id
  FROM price
  WHERE rubric_id IN (r.id, r.id2, r.id3)
  )

@dubok79 · 21.11.2014, 15:27

Все так, но меня коробит от таких запросов, но в силу того, что я привык использовать хранимые процедуры, а на бесплатных СУБД их пока нет, хотя MySQL уже, что пытается делать - молодцы.
А вообще лучше вместо вложенного запроса делать вьюхи, т.к. читабельность кода повышается и ошибки искать проще. Ну а если вместо EXISTS сделать INNER JOIN по вьюхе запроса находящегося в EXISTS, то можно еще увеличить скорость, если прикрутить нужные индексы.

@dubok79 325 / 123 / 10 Регистрация: 01.11.2012 Сообщений: 586
	31.10.2014, 21:57	2
	Никогда не видел такого запроса, возможно это фича именно SQLite. В каком виде у вас таблица? Обычно когда строят дерево то строится одна ветвь и подгружается дочерняя. При разворачивании процесс повторяется. Или вам надо как по ссылке чисто показать результат в виде дерева? Добавлено через 6 минут Не знаю как в SQLite одним запросом сделать. В ней нет хранимых процедур, но можно обойти это кодом в самом проекте. Т.е. результат формировать в некую временную табличку, а потом ее вывести. Можно рекурсивно, а можно по цепочке опускаться вниз, пока не найдете самую длинную ветку. Мы на MSSQL обычно так делаем. 1

@CoolMind 426 / 406 / 68 Регистрация: 06.10.2012 Сообщений: 1,748
	31.10.2014, 22:32 [ТС]	3
	dubok79, спасибо за ответ. Я не так часто обращаюсь нынче к СУБД, раньше чаще. Конечно же, возможность создания рекурсивных запросов есть и в Oracle, и в SQL Server, а теперь есть и в SQLite. Но, как обычно, до Андроида прогресс ещё не дошёл. Хорошо хоть для Андроида есть нормальные IDE типа Android Studio и IntelliJ IDEA, а то было бы совсем тяжко. Недавно пробовал делать импорт базы при помощи Java, скачал NetBeans, так после Android Studio показалась поделкой проще Eclipse. Ещё и библиотеки пришлось скачивать. Да, что-то инструментарий слабоват. По теме запроса я пока решил так, что, поскольку уровень вложенности не превышает 2, сделал несколько вложенных запросов. Мне надо просто единым списком показать все позиции (без сворачивания-разворачивания узлов). Соответственно, тема эта была создана для того, чтобы люди обратили внимание на слабость инструментария. Добавлено через 8 минут К сожалению, пока не могу найти ссылку на stackoverflow, где я взял пример запроса. Вот описание от SQLite: http://www.sqlite.org/lang_with.html, там же интересные примеры. 1

@dubok79 325 / 123 / 10 Регистрация: 01.11.2012 Сообщений: 586
	31.10.2014, 22:43	4
	Еще я такие вещи делаю по полю HistoryID. В нем хранится весь путь где лежит позиция. Формировать его проще простого. Делать поиск еще проще. Например выглядит так: 1.23.4. Где 1 это ид самой верхней папки, 23 ид родительской папки, а 4 собственная идешка. 1

@CoolMind 426 / 406 / 68 Регистрация: 06.10.2012 Сообщений: 1,748
	31.10.2014, 22:50 [ТС]	5
	dubok79, да, знакомая тема. Согласен, в сложных (или медленных) запросах иногда полезно хранить избыточные данные, дополнительные поля. Проблема только в том, чтобы эти данные всегда были правильными, а это значит, что надо периодически их обновлять (или делать триггеры на обновление). Добавлено через 2 минуты Может возникнуть вопрос, а что же здесь обновлять? Ситуации бывают разные, например, перенос вершины в другое место (переподчинение) или изменение порядка следования вершин в одном поддереве. 0

@dubok79 325 / 123 / 10 Регистрация: 01.11.2012 Сообщений: 586
	31.10.2014, 22:55	6
	Сообщение от CoolMind Может возникнуть вопрос, а что же здесь обновлять? Ситуации бывают разные, например, перенос вершины в другое место (переподчинение) или изменение порядка следования вершин в одном поддереве. Ну это все конечно же надо описывать в своем коде. Конечно же я думаю всегда про большие таблички Такая уж у меня работа. 0

@CoolMind 426 / 406 / 68 Регистрация: 06.10.2012 Сообщений: 1,748
	21.11.2014, 11:30 [ТС]	7
	dubok79, спасибо за напоминание о дополнительных полях. Возьму на вооружение при случае. В других проектах раньше тоже так делал, затем использовал LIKE для отбора нужных вершин. Вчера делал снова запрос, но он очень долго выполнялся (из-за вложенностей), и я решил переписать. Открыл пару сайтов: http://habrahabr.ru/post/43955/ и http://www.sql.ru/forum/104183... -al-derevo, вспомнил, что при известном максимальном уровне вложенности можно обойтись без рекурсии (как во втором сайте). Получается быстро. 0

@dubok79 325 / 123 / 10 Регистрация: 01.11.2012 Сообщений: 586
	21.11.2014, 13:11	8
	Рекурсия это вообще хорошо, но плохо. Долго работает, как впрочем и курсоры. 0

@dubok79 325 / 123 / 10 Регистрация: 01.11.2012 Сообщений: 586
	21.11.2014, 15:27	10
	Все так, но меня коробит от таких запросов, но в силу того, что я привык использовать хранимые процедуры, а на бесплатных СУБД их пока нет, хотя MySQL уже, что пытается делать - молодцы. А вообще лучше вместо вложенного запроса делать вьюхи, т.к. читабельность кода повышается и ошибки искать проще. Ну а если вместо EXISTS сделать INNER JOIN по вьюхе запроса находящегося в EXISTS, то можно еще увеличить скорость, если прикрутить нужные индексы. 1