从嵌套集(perl，sql，jquery)生成JSON [英] Generate JSON from nested sets (perl, sql, jquery)

问题描述

我在数据库中有内容页面(使用嵌套集)，我需要通过jQuery jsTree插件来显示它.需要使用以下数据返回JSON:

I have content pages in the database (using nested sets) and I need to show it by jQuery jsTree plugin. It's need to return JSON with data like this:

[
    {
        data: 'node1Title',
        children: [
            {
                data: 'subNode1Title',
                children: [...]
            },
            {
                data: 'subNode2Title',
                children: [...]
            }
        ]
    },
    {
        data: 'node2Title',
        children: [...]
    }
]

我需要做什么?

我可以将哈希数组转换为JSON，但是我不知道如何生成数组.

I can transform an array of hashes to JSON but I don't understand how to generate an array.

样本数据:

**'pages'table**
id  parent_id   level   lkey    rkey    name
1   0       1   1   14  index
2   1       2   2   7   info
3   1       2   8   13  test
4   2       3   3   4   about
5   2       3   5   6   help
6   3       3   9   10  test1
7   3       3   11  12  test2

我需要得到:

[
    {
        data: 'index',
        children: [
            {
                data: 'info',
                children: [
                    {
                        data: 'about'
                    },
                    {
                        data: 'help',
                    }
                ]
            },
            {
                data: 'test',
                children: [
                    {
                        data: 'test1'
                    },
                    {
                        data: 'test2'
                    }
                ]
            }
        ]
    }
]

推荐答案

我遇到了完全相同的问题，这是我在Perl中编写的将嵌套集树转换为jsTree插件的JSON对象的方法(我正在使用DBIx :: Tree :: NestedSet访问MySQL数据库树).从Perl的角度来看，我知道我的代码很丑陋，但这对我有用.

I had exactly the same problem and here is what I wrote in Perl to convert my nested set tree into a JSON object for jsTree plugin (I'm using DBIx::Tree::NestedSet to access the MySQL database tree). I know my code is ugly from a Perl perspective, but it works for me.

sub get_json_tree {
    my $json = '[';
    my $first = 1;
    my $last_level = 1;
    my $level = 1;
    my $tree = DBIx::Tree::NestedSet->new(dbh => $dbh);
    my $ancestors = $tree->get_self_and_children_flat(id => $tree->get_root);
    foreach (@{$ancestors}) {
        my $name = $_->{'name'};
        $last_level = $level;
        $level = $_->{'level'};
        if ($level > $last_level) {
            $json .= ',' if ($json =~ /}$/);
        } elsif ($level < $last_level) {
            $json .= ']}';
            for (my $i = 0; $i < $last_level - $level; $i++) {
                $json .= ']}';
            }
            $json .= ',';
        } elsif ($level == $last_level && !$first) {
            $json .= ']},';
        }
        $json .= '{"attr":{"id":'.$_->{'id'}.',"rel":"folder"},"data":"'.$name.'","children":[';
        $first = 0;
    }
    $json .= ']}';
    for (my $i = 1; $i < $level; $i++) {
        $json .= ']}';
    }    
    $json .= ']';
    return $json;
}

这篇关于从嵌套集(perl，sql，jquery)生成JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！