对PostgreSQL实现递归查询教程

介绍

在Nilenso，弟弟正在研究（开放源代码！）设计和展开调查的应用。

下面是一个调查的例子。
在内部，它是这样一个下降：
调查包括了许多问题（问题）。一系列的问题可以分为一类（catery）。我们实际的数据结构是一个比较复杂的（尤其是子问题的子问题的一部分），但首先是唯一的问题，catery。
我们把问题和catery这样。

每一个问题，catery有order_number领域。它是一个整数，用于与其他兄弟指定的相对关系。

例如，上面的调查：
酒吧的order_number低于巴兹。

此类分类中的问题可以以正确的顺序出现：

在catery.rb #

DEF sub_questions_in_order
Questions.order（'order_number）
终点
In fact, we started the whole survey of fetch.Each catery gets the entire sub problem in order, and then the whole entity tree is traversed by the analogy.

这给出了整个树的深度优先顺序：
对超过5层的嵌入式和超过100个问题的调查是非常缓慢的。

递归查询

我的弟弟也用宝石如awesome_nested_set，但不是其中之一，据我所知，是支持多模型存取。

后来哥哥无意中发现一个文档，PostgreSQL已经支持递归查询！嗯，这可能是。

然后试着用它来进行递归查询（同时，兄弟对它的理解是水，不到位，不喷洒）。

在Postgres做递归查询，你必须定义一个初始化查询，这是一个非递归部分。

在这种情况下，它是最高的问题和catery。顶部的元素没有一个父分类，所以他们的catery_id是空的。

（
选择ID、内容、order_number，类型，catery_id问题
在questions.survey_id = 2和questions.catery_id是空的
）
联盟
（
选择ID、内容、order_number，类型，catery_id从伙伴关系
在cateries.survey_id = 2和cateries.catery_id是空的
）
（此查询和以下查询假定id的调查为2）

这是元素的最高级别。
递归部分写在下面。根据下面的Postgres的文件：
递归部分得到在初始化前一部分得出的元素的所有子项。

用递归first_level_elements（如
-非递归项
（
（
选择ID、内容、order_number，catery_id问题
在questions.survey_id = 2和questions.catery_id是空的
联盟
选择ID、内容、order_number，catery_id从伙伴关系
在cateries.survey_id = 2和cateries.catery_id是空的
）
）
联盟
递归术语
选择q.id，q.content，q.order_number，q.catery_id
从first_level_elements FLE，问题问
在q.survey_id = 2和q.catery_id = fle.id
）
SELECT * FROM first_level_elements；
因此，递归部分只能得到的问题。如果一个分项第一子是一个分类Postgres不给超过一参考非递归的项目。所以这是不好的问题上，工会和catery结果集。我们要在这里做一个改变。

用递归first_level_elements（如
（
（
选择ID、内容、order_number，catery_id问题
在questions.survey_id = 2和questions.catery_id是空的
联盟
选择ID、内容、order_number，catery_id从伙伴关系
在cateries.survey_id = 2和cateries.catery_id是空的
）
）
联盟
（
选择e.id，内容，e.order_number，e.catery_id
从
（
把问题和伙伴关系
选择ID、内容、order_number，catery_id从问题的地方survey_id = 2
联盟
选择ID、内容、order_number，catery_id从伙伴关系，survey_id = 2
E、first_level_elements FLE）
在e.catery_id = fle.id
）
）
SELECT * FROM first_level_elements；
catery和问题的结果在非递归部分设置为联盟加入。

这就产生了调查的所有要素：
不幸的是，订单似乎错了。
递归查询中的排序

问题是，尽管所有的两个级别元素都是为第一级元素获得的，但这是一种广度优先搜索。事实上，我们首先需要深度。

这是怎么做到的呢

Postgres已经建立阵列的查询能力。

然后为要存储的元素的序列号构建一个数组：

路径（如果有的话）的父亲分类+自己的order_number

如果将结果集与路径排序，可以首先将查询转换为深度！

用递归first_level_elements（如
（
（
选择ID、内容、catery_id，阵列{id}路径问题
在questions.survey_id = 2和questions.catery_id是空的
联盟
选择ID、内容、catery_id，阵列{id}路径从伙伴关系
在cateries.survey_id = 2和cateries.catery_id是空的
）
）
联盟
（
选择e.id，内容，e.catery_id（fle.path e.id | |）
从
（
选择ID、内容、catery_id，从问题的地方survey_id = 2 order_number
联盟
选择ID、内容、catery_id，从伙伴关系，survey_id = 2 order_number
E、first_level_elements FLE）
在e.catery_id = fle.id
）
）
选择的路径从first_level_elements秩序*；
它离成功很近，但有两首你最喜欢的歌

这是通过ID找到子项引起的。

在e.catery_id = fle.id
文件包括问题和catery。但是我们真正需要的是只有catery匹配（因为问题没有訾翔）。

然后硬编码类型（类型）为每个这些查询，所以你不必去检查是否有任何子项的问题。

用递归first_level_elements（如
（
（
选择ID、内容、catery_id，'questions'as类型、数组{id}路径问题
在questions.survey_id = 2和questions.catery_id是空的
联盟
选择ID、内容、catery_id，'cateries'as类型、数组{id}路径从伙伴关系
在cateries.survey_id = 2和cateries.catery_id是空的
）
）
联盟
（
选择e.id，内容，e.catery_id，e.type（fle.path e.id | |）
从
（
选择ID、内容、catery_id，'questions'as型，从问题的地方survey_id = 2 order_number
联盟
选择ID、内容、catery_id，'cateries'as型，从伙伴关系，survey_id = 2 order_number
E、first_level_elements FLE）
寻找孩子只有型is'cateries
在e.catery_id = fle.id和fle.type = 'cateries
）
）
SELECT * from first_level_elements ORDER BY path;

看起来不错，做吧！

下面看看性能如何。
使用下面的脚本（在创建接口的一个调查之后），兄弟生成了10个子问题序列，每个序列有6层。

调查组survey.find（9）
10.times做
catery = factorygirl.create（：catery，：调查= >调查）
6.times做
catery = factorygirl.create（：：catery，catery = > catery，：调查= >调查）
终点
factorygirl.create（：：single_line_question，catery_id = > catery。ID：survey_id = >调查。ID）
终点
每个问题的顺序似乎都是这样的。
让我们看看递归查询是否比启动查询快一点。

撬（主）> benchmark.ms { 5.times { survey.find（9）。sub_questions_using_recursive_queries } }
= > 36.839999999999996

撬（主）> benchmark.ms { 5.times { survey.find（9）。sub_questions_in_order } }
= > 1145.1309999999999
快31倍多太好了。