How can I expose many-to-many tag-style relationship in a Catalyst app?

Question

I'm building a database application in Catalyst, using jqGrid to do the messy work of handling the display of data. I've got almost everything working, except for being able to filter search results by "tags". I have three tables, with relationships like this:

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;

and

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

and finally, the join table

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" },
    { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);
...
1;

In my controller that provides the JSON data to jqGrid, I use Catalyst::TraitFor::Controller::jQuery::jqGrid::Search to translate the request parameters generated by jqGrid into DBIx::Class-style queries:

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',
    },
);

which is then passed on to the jqGrid page generator:

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

and then I iterate over the result set and build my jqGrid columns.

On the HTML side, I am able to build a JSON string like

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

and, in this case, show Projects having a row in project_flags with flag id of 2.

I absolutely know I'm not doing this correctly! All of the documentation I can find on Catalyst and DBIx::Class demonstrates similar ideas, but I just can't understand how to apply them to this situation (not that I haven't tried).

• How would I go about building "has_flag($flag_id)"-type accessors, and then be able to use them from within jqGrid's API? Where in my Catalyst app would this belong?
• One of the ways I'd like to filter is by the lack of a particular flag also.

Answer 1

I've got to be honest with you, I'm not entirely sure I understand your question. It seems to be what you're asking has more to do with DBIx::Class than Catalyst--the latter I know very little about, the former I am learning more about every day. With that in mind, here's my best attempt at answering your question. I am using Mojolicious as the MVC, since that's what I know best.

First, I start by creating a many-to-many database:

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,name) VALUES (1,'flag1');
INSERT INTO flag (id,name) VALUES (2,'flag2');
INSERT INTO flag (id,name) VALUES (3,'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,flag_id) VALUES (1,2);
INSERT INTO project_flag (project_id,flag_id) VALUES (1,3);
INSERT INTO project_flag (project_id,flag_id) VALUES (1,4);
INSERT INTO project_flag (project_id,flag_id) VALUES (2,1);
INSERT INTO project_flag (project_id,flag_id) VALUES (2,4);

And here is my Perl (Mojolicious) code:

#!/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;

And here's the JSON output (pretty print) from project1 (has flags related to it):

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

And here is JSON for project3, with no relationship to any flags:

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

I put the files on Github, so you can check them out if you'd like.

In your given situation, say they've typed the word 'c++' into the filter, and you want to return everything that has been tagged 'c++', then:

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

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

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

If you wanted to return all columns, use prefetch rather than join. Also, if you'd like to support the Autosearch functionality, change search to search_like, like so:

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

I hope that if I did not answer your question what I have given is at least a push in the right direction.