recursively inflate columns

Question

I have a many to many relationship between 3 tables User, Role and Project. This results in a mapping table User_has_Roles_has_Projects Now In my code I can get to the mapping table by writing @a=$s->resultset('User')->find({idUser=>1})->user_has_roles_has_projects->all

I would like to get something equivalent to a left join of the mapping table with the other 3 tables. i.e.

idUser|name
1     |Vijay

idRole|role
1     | Manager
2     | Developer

idProject |prj|path
1         |foo | /prj/foo
2         | bar| /prj/bar

and mapping table as

idUser|idRole|idProject
1     | 2    | 2

The query should give me

idUser|idRole|idProject|name|role|prj|path
1     | 2    | 2       | Vijay|Developer|bar|/prj/bar

I would like to have the above as an either a simple AOH or an AOA. The closest that I can come to the above is by calling get_inflate_columns on each row of the mapping table and then again calling get_inflate_columns on each values of the result. and finally merging the result in the desired format. i.e. something similar to

   foreach my $idx (@a){
    my %b=$a[0]->get_inflated_columns
        foreach my $val (keys %b){
       @val= $b{$val}->get_columns 
       $b{$val}=\@val

    }
  push @result,\%b 
}
 # Post process @result to get a simple AOA or AOH

Is there a better way to achieve the above result?

daxim · Answer 1 · 2018-04-16T09:49:10.067

Use DBIx::Class::Helper::Row::ToJSON. Calling TO_JSON gives you a hashref of resultclass objects, which neatly serialises recursively. See output in comments below.

use DBIx::Class::Schema::Loader qw();
require DBIx::Class::Helper::Row::ToJSON;
use JSON::MaybeXS qw();

DBIx::Class::Schema::Loader->loader_options(
    naming => undef,
    preserve_case => 1,
    moniker_map => sub { shift->name },
    col_accessor_map => sub { shift->name },
    rel_name_map => sub {
        my $t = shift;
        $t->{type} eq 'belongs_to'
            ? $t->{local_columns}[0]
            : $t->{remote_moniker}
    },
);
my $schema = DBIx::Class::Schema::Loader->connect(
    'DBI:Pg:', (undef) x 2, {quote_names => 1}
);
for my $sourcename ($schema->sources) {
    my $src = $schema->source($sourcename);
    $src->result_class->load_components('Helper::Row::ToJSON');
    for my $colname ($src->columns) {
        $src->{_columns}{$colname}{is_serializable} = 1
            if 'text' eq $src->column_info($colname)->{data_type};
    }
};

# hashref of resultclass objects
my $h = $schema->resultset('users')->find({idUser=>1})->user_has_roles_has_projects->first->TO_JSON;
# {
#     idProject => DBIx::Class::Schema::Loader::Result::projects  {
#         internals: {
#             _column_data     {
#                 idProject   2,
#                 path        "/prj/bar",
#                 prj         "bar"
#             },
#             _in_storage      1,
#             _result_source   DBIx::Class::ResultSource::Table
#         }
#     },
#     idRole => DBIx::Class::Schema::Loader::Result::roles  {
#         internals: {
#             _column_data     {
#                 idRole   2,
#                 role     "Developer"
#             },
#             _in_storage      1,
#             _result_source   DBIx::Class::ResultSource::Table
#         }
#     },
#     idUser => DBIx::Class::Schema::Loader::Result::users  {
#         internals: {
#             _column_data     {
#                 idUser   1,
#                 name     "Vijay"
#             },
#             _in_storage      1,
#             _result_source   DBIx::Class::ResultSource::Table
#         }
#     }
# }

print JSON::MaybeXS->new(convert_blessed => 1, pretty => 1)->encode($h);

# {
#     "idUser" : {
#         "name" : "Vijay",
#         "idUser" : 1
#     },
#     "idProject" : {
#         "idProject" : 2,
#         "path" : "/prj/bar",
#         "prj" : "bar"
#     },
#     "idRole" : {
#         "role" : "Developer",
#         "idRole" : 2
#     }
# }

__END__
create table "users" (
    "idUser" bigint primary key,
    "name" text not null
);
insert into "users" ("idUser", "name") values (1, 'Vijay');
create type "e_role" as enum ('Manager', 'Developer');
create table "roles" (
    "idRole" int primary key,
    "role" e_role null
);
insert into "roles" ("idRole", "role") values (1, 'Manager');
insert into "roles" ("idRole", "role") values (2, 'Developer');
create table "projects" (
    "idProject" int primary key,
    "prj" text not null,
    "path" text not null
);
insert into "projects" ("idProject", "prj", "path") values (1, 'foo', '/prj/foo');
insert into "projects" ("idProject", "prj", "path") values (2, 'bar', '/prj/bar');
create table "user_has_roles_has_projects" (
    "idUser" int references "users" ("idUser"),
    "idRole" int references "roles" ("idRole"),
    "idProject" int references "projects" ("idProject")
);
insert into "user_has_roles_has_projects" ("idUser", "idRole", "idProject") values (1, 2, 2);

recursively inflate columns

1 Answers1