Trying to get to grips with references and joins in DBIx
Let us assume I have the following three Schema classes set up:
A web-based service:
---- file 1 ----
package ORI::Schema::Result::Repo;
extends 'DBIx::Class::Core';
__PACKAGE__->table("repo");
__PACKAGE__->add_columns(
"id" => { data_type => "integer" },
"system_url" => { data_type => "text" },
"oaibaseurl" => { data_type => "text" },
"lat" => { data_type => "real" },
"long" => { data_type => "real" },
"mandate" => { data_type => "boolean" },
"fulltext" => { data_type => "boolean" },
"openaccess" => { data_type => "boolean" },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
"contentlinks" => "ORI::Schema::Result::Contentlink",
{ "foreign.repo_id" => "self.id" },
);
__PACKAGE__->many_to_many("contents", "contentlinks", "content");
A list of types of content the service deals with (can be multiple data types):
---- file 2 ----
package ORI::Schema::Result::Contenttype;
extends 'DBIx::Class::Core';
__PACKAGE__->table("contenttypes");
__PACKAGE__->add_columns(
"id" => { data_type => "integer" },
"text" => { data_type => "text" },
);
__PACKAGE__->set_primary_key("id");
_PACKAGE__->has_many(
"contentlinks" => "ORI::Schema::Result::Contentlink",
{ "foreign.content_id" => "self.id" },
);
__PACKAGE__->many_to_many("repos", "contentlinks", "repo");
Finally, a table to link web services to data types:
---- file 3 ----
package ORI::Schema::Result::Contentlink;
extends 'DBIx::Class::Core';
_PACKAGE__->table("contentlinks");
__PACKAGE__->add_columns(
"content_id" => { data_type => "integer" },
"repo_id" => { data_type => "integer" },
);
__PACKAGE__->set_primary_key("content_id", "repo_id");
__PACKAGE__->belongs_to(
"content" => "ORI::Schema::Result::Contenttype",
{ id => "content_id" },
);
__PACKAGE__->belongs_to(
"repo" => "ORI::Schema::Result::Repo",
{ id => "repo_id" },
);
So, in code (a Catalyst Controller, in my case), I can get the ORI::Schema::Result::Repo values using the Template Toolkit:
---- within controller ----
# Get the first 10 results (getting all crashes the system! )
$c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
{}
{
rows => '10'
}
)
] );
---- In Template ----
[% FOREACH repo IN repos -%]
<tr>
<td>[% repo.oaibaseurl %]</td>
<td>[% repo.system_url %]</td>
<td>[% repo.lat %]</td>
<td>[% repo.long %]</td>
<td>[% repo.opstatus %]</td>
<td>[% repo.fulltext %]</td>
<td>[% repo.mandate %]</td>
[% END -%]
.... but how do I get a list of all the data types the web-service is linked to?
---- within controller ----
# Get the first 10 results (getting all crashes the system! )
# Add in a join to get all the contents (ManyToMany relationship)
$c->stash( repos => [ $c->model('ORIdatabase::Repo')->search(
{}
{
join => 'contents',
prefetch => 'contents',
rows => '10'
}
)
] );
---- In Template ----
[% FOREACH repo IN repos -%]
<tr>
<td>[% repo.oaibaseurl %]</td>
<td>[% repo.system_url %]</td>
<td>[% repo.lat %]</td>
<td>[% repo.long %]</td>
<td>[% repo.opstatus %]</td>
<td>[% repo.fulltext %]</td>
<td>[% repo.mandate %]</td>
<td>[% repo.mandate %]</td>
<td>
[% # How to I get the list of content types? -%]
</td>
[% END -%]
.... however this barfs! Using the 'content' or 'contents' relationships fail, and 'contentlinks' just goes down 1 level!!
How do I get the list of text fields from ORI::Schema::Result::Contenttype, where they are linked by ORI::Schema::Result::Contentlink?
-- Ian Stuart
A man depriving some poor village, somewhere, of a first-class idiot.