搜索分层数据的最佳方法

I'm looking at building a facility which allows querying for data with hierarchical filtering. I have a few ideas how I'm going to go about it but was wondering if there are any recommendations or suggestions that might be more efficient.

我正在寻找建立一个允许使用分层过滤查询数据的工具。我有一些想法,我将如何去做,但想知道是否有任何建议或建议可能更有效。

As an example imagine that a user is searching for a job. The job areas would be as follows.

例如,假设用户正在搜索作业。工作领域如下。

1: Scotland
2: --- West Central
3: ------ Glasgow
4: ------ Etc
5: --- North East
6: ------ Ayrshire
7: ------ Etc

A user can search specific (i.e. Glasgow) or in a larger area (i.e. Scotland).

用户可以搜索特定的(即格拉斯哥)或更大的区域(即苏格兰)。

The two approaches I am considering are:

我正在考虑的两种方法是:

keep a note of children in the database for each record (i.e. cat 1 would have 2, 3, 4 in its children field) and query against that record with a SELECT * FROM Jobs WHERE Category IN Areas.childrenField.

在每个记录的数据库中记下子项的注释(即cat 1在其子字段中将有2,3,4)并使用SELECT * FROM Jobs WHERE Category IN Areas.childrenField查询该记录。

Use a recursive function to find all results who have a relation to the selected area.

使用递归函数查找与所选区域有关系的所有结果。

The problems I see from both are:

我从两者看到的问题是:

Holding this data in the db will mean having to keep track of all changes to structure.

在数据库中保存此数据意味着必须跟踪结构的所有更改。

Recursion is slow and inefficent.

递归是缓慢而无效的。

Any ideas, suggestion or recommendations on the best approach? I'm using C# ASP.NET with MSSQL 2005 DB.

关于最佳方法的任何想法,建议或建议?我正在使用C#ASP.NET和MSSQL 2005 DB。

5 个解决方案

#1

Here is an approach i have seen used:

这是我见过的一种方法:

Create a varchar(max) field called hierarchyid. Generate base ids for all root objects. For each child object generate an id and prepend it with the parent(s) ids.

创建名为hierarchyid的varchar(max)字段。为所有根对象生成基本ID。为每个子对象生成一个id,并在父ID之前添加它。

Example Table

ID(PK) HierarchyID Area
1       sl           Scotland 
2       slwc        West Central
3       slwcgg       Glasgow

Example Query

SELECT * FROM Areas Where HierarchyID LIKE 'sl%'

#2

You should use nested sets. Here's an implementation in MySQL. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

您应该使用嵌套集。这是MySQL中的一个实现。 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

#3

You can use Common Table Expressions to do recursive queries. I find this technique very powerful, easy to read and easy to maintain.

您可以使用公用表表达式执行递归查询。我发现这种技术非常强大,易于阅读和易于维护。

#4

How about this?

这个怎么样?

Table =>

Id ParentId Name

Id ParentId名称

Nice simple table?

好简单的桌子?

Then how about some nice complicated piece pf SQL to go with that? (CTEs rock I think)

那么一些不错的复杂的片段如何与SQL一起去? (我认为CTE摇滚)

public object FetchCategoryTree()
{
    var sql = @"SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    WITH AreaTree (ID, Name, ParentID, OrgLevel, SortKey) AS
    (
        -- Create the anchor query. This establishes the starting
        -- point
        SELECT
            a.ID,
            cast('---- ' + a.Name as varchar(255)),
            a.ParentID,
            cast('----' as varchar(55)),
            CAST(a.ID AS VARBINARY(900))
        FROM dbo.Area a
        WHERE a.ParentID is null
        UNION ALL
        -- Create the recursive query. This query will be executed
        -- until it returns no more rows
        SELECT
            a.ID,
            cast('----' + b.OrgLevel + '  ' + a.Name as varchar(255)),
            a.ParentID,
            cast(b.OrgLevel+ '----' as varchar(55)),
            CAST(b.SortKey + CAST (a.ID AS BINARY(4)) AS VARBINARY(900))
        FROM dbo.Area a
                INNER JOIN AreaTree b ON a.ParentID = b.ID
    )
    SELECT * FROM AreaTree
    ORDER BY SortKey";

    return FetchObject(sql);
}

Now this does some SQL magic that am not too sure of. However in layman's terms it basically takes the first part as the root query. Then it goes back to the table and executes the second part using the first part's answer through a join, and continues doing to still it can't find any more matches, basically a big loop. It's pretty quick as well.

现在这做了一些不太确定的SQL魔术。然而,在外行人的术语中,它基本上将第一部分作为根查询。然后它返回到表并使用第一部分通过连接的答案执行第二部分,并继续执行仍然无法找到更多匹配,基本上是一个大循环。它也很快。

You will get the out a bunch of rows with a sort key attached. Once you order the query by the sort key you will get the answer like :

你会得到一堆附有排序键的行。通过排序键订购查询后,您将得到如下答案:

 ---- parent 1
 -------- child 1
 -------- child 2
 ------------ child 2.1
 ---- parent 2
 -------- etc

Might be what you are looking for?

你可能正在寻找什么?

#5

I use Joe Celko's tree model for the sales tax hierarchy (state/county/city/misc) in our application and it works well.

我在我们的应用程序中使用Joe Celko的树模型作为销售税层次结构(州/县/市/ misc)并且运行良好。

Your "find jobs at this area or below" query would look something like this:

您的“在此区域或以下位置找工作”查询看起来像这样:

SELECT * FROM Jobs WHERE Jobs.AreaID IN
(SELECT P1.AreaID
FROM Areas AS P1, Areas AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.Areas.AreaID = @selectedAreaID)

Celko Tree in SQL article

SQL文章中的Celko Tree

#1