perl – 如何在Catalyst应用程序中公开多对多标记样式的关系？

package MyApp::Schema::Result::Project;
...
__PACKAGE__->has_many(
    "job_flags","MyApp::Schema::Result::ProjectFlag",{ "foreign.project_id" => "self.id" },{ cascade_copy => 0,cascade_delete => 0 },);
...
__PACKAGE__->many_to_many(flags => 'project_flags','flag');
1;

package MyApp::Schema::Result::Flag;
...
__PACKAGE__->has_many(
     "project_flags",{ "foreign.flag_id" => "self.id" },);
...
__PACKAGE__->many_to_many(projects => 'project_flags','project');
1;

package MyApp::Schema::Result::ProjectFlag;
...
__PACKAGE__->belongs_to(
    "flag","MyApp::Schema::Result::Flag",{ id => "flag_id" },{ is_deferrable => 1,on_delete => "CASCADE",on_update => "CASCADE" },);
...
__PACKAGE__->belongs_to(
    "project","MyApp::Schema::Result::Project",{ id => "project_id" },);
...
1;

my $search_filter = $self->jqGrid_search($c->req->params);
my $project_rs = $c->model('DB::Project')->search(
    $search_filter,{
        join => 'project_flags',group_by => 'id',},);

$project_rs = $self->jqgrid_page($c,$project_rs);

{"groupOp":"AND","rules":[{"field":"project_flags.flag_id","op":"eq","data":"2"}]}

我必须对你诚实,我不完全确定我理解你的问题.看起来你要问的是DBIx :: Class比Catalyst更多 – 后者我知之甚少,前者我每天都在学习更多.考虑到这一点,这是我回答你的问题的最佳尝试.我使用Mojolicious作为MVC,因为这是我最了解的.

首先,我首先创建一个多对多数据库：

CREATE TABLE project(
  id INTEGER PRIMARY KEY,name text
);

CREATE TABLE flag(
  id INTEGER PRIMARY KEY,name text
);

CREATE TABLE project_flag(
  project_id integer not null,flag_id integer not null,FOREIGN KEY(project_id) REFERENCES project(id),FOREIGN KEY(flag_id) REFERENCES flag(id)
);

INSERT INTO project (id,name) VALUES (1,'project1');
INSERT INTO project (id,name) VALUES (2,'project2');
INSERT INTO project (id,name) VALUES (3,'project3');

INSERT INTO flag (id,'flag1');
INSERT INTO flag (id,'flag2');
INSERT INTO flag (id,'flag3');
INSERT INTO flag (id,name) VALUES (4,'flag4');

INSERT INTO project_flag (project_id,flag_id) VALUES (1,1);
INSERT INTO project_flag (project_id,2);
INSERT INTO project_flag (project_id,3);
INSERT INTO project_flag (project_id,4);
INSERT INTO project_flag (project_id,flag_id) VALUES (2,4);

这是我的Perl(Mojolicious)代码：

#!/usr/bin/env perl
use Mojolicious::Lite;
use Schema;

helper db => sub {
    return Schema->connect('dbi:SQLite:test.db');
};

get '/' => sub {
    my $self = shift;


    my $rs = $self->db->resultset('Project')->search(
        { 'me.name' => 'project3' },{
            join      =>  { 'project_flags' => 'flag' },select => ['me.name','flag.name'],as     => ['project','flag']
        }
    );

    $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');

    $self->render( json => [ $rs->all ] );
};

app->start;

这是来自project1的JSON输出(漂亮的打印)(有与之相关的标志)：

[
   {
      "project":"project1","flag":"flag1"
   },{
      "flag":"flag2","project":"project1"
   },{
      "project":"project1","flag":"flag3"
   },{
      "flag":"flag4","project":"project1"
   }
]

这里是project3的JSON,与任何标志都没有关系：

[
   {
      "project":"project3","flag":null
   }
]

我把文件放在Github上,所以如果你愿意的话可以check them out.

在你给定的情况下,假设他们在过滤器中输入了“c”这个词,你想要返回标记为“c”的所有内容,然后：

my $rs = $self->db->resultset('Tag')->search(
    { 'me.name' => 'c++' },{
        join      =>  { 'project_flags' => 'project' },'project.name'],as     => ['tag','project']
    }
);

$rs->result_class('DBIx::Class::ResultClass::HashRefInflator');

$self->render( json => [ $rs->all ] );

如果要返回所有列,请使用prefetch而不是join.此外,如果您想支持自动搜索功能,请将搜索更改为search_like,如下所示：

my $rs = $self->db->resultset('Tag')->search_like(
    { 'me.name' => $search_filter.'%' },

我希望如果我没有回答你的问题,我所提出的至少是朝着正确的方向发展.