# DBIC Custom ResultSource for Arbitrary SQL
# DBIC Virtual View for Arbitrary SQL
So you haven't figured out how to write a complex query in DBIC yet? No worries, DBIC will allow you to write that query in SQL. To do so one:
So you haven't figured out how to write a complex query in DBIC yet? No worries, DBIC will allow you to write that query in SQL. To do so we use a facility called 'virtual views'. These are very similar to a regular Result class, except that you also add the SQL which is the view definition.
* Defines a custom ResultSource based on an existing one.
<pre>
* Pass the new ResultSource's name method the raw SQL.
package My::Schema::Result::StudentSectionScoresbyAdmin;
* Register the new ResultSource with your Schema.
use strict;
use warnings;
use base qw/DBIx::Class::Core/;
## Define a new ResultSource
__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
One has to decide where to put the new ResultSource definition. One place is to put it in an existing ResultSource. Choose a ResultSource who's table is involved in your query and perhaps the ResultSource that has the most fields in your query selection. Since I'm interested in selecting section scores, I am going to put the new ResultSource definition in my pre-existing ResultSource SectionScore.pm . Here's the code:
__PACKAGE__->table('roar.tbl_section_scores');
<pre>
my $result_source = __PACKAGE__->result_source_instance();
my $new_result_source = $result_source->new( $result_source );
$new_result_source->source_name( 'StudentSectionScoresbyAdmin' );
</pre>
This defines an instance of the existing ResultSource, then uses that instance to create a copy of itself as a new ResultSource. To distinguish the new ResultSource from the existing I give it a unique name with the _source_name_ method.
The '->table' call must be present, but the value is not important.
## Pass SQL to New ResultSource
### Match new ResultSource Columns with Raw SQL Query Columns
The SQL to generate our virtual table (view) is just the raw SQL we'd use to perform the query[1].
The ResultSet is constructed for a query of type:
We pass it as a here doc to the _name_ method.
<pre>
$new_result_source->name( \&lt;&lt;SQL );
select _all_columns_of_new_result_source_
(
from _virtual_table_
select
sec_scores.*, first_name, last_name
from
roar.tbl_section_status as sec_status,
roar.tbl_section_scores as sec_scores,
people.tbl_students as students,
people.tbl_people as people
where
sec_status.section=sec_scores.section
and sec_status.student=sec_scores.student
and sec_scores.student=students.id
and students.person=people.id
and sec_status.status_type=5
and sec_scores.retest=0
and sec_scores.section in
(select id from assessment.tbl_sections as sections
where sections.admin = ?)
)
SQL
</pre>
### Match new ResultSource Columns with Raw SQL Query Columns
This means you better make sure the columns in the new ResultSource (at the very least) are a subset of those in the virtual table.
The ResultSet is constructed for a query of type:
select _all_columns_of_new_result_source_ from _virtual_table_
This means you better make sure the columns in the new ResultSource (at the very least) are a subset of those in the virtual table. Most likely you probably want equivalent set of columns. The DBIx::Class::ResultSource methods _add_columns_ and possibly _remove_columns_ are your friends here. For example, I want first_name and last_name columns to be in the ResultSet, and since they live in a table different from roar.tbl_section_scores (the columns that come with the creation of the new ResultSource) I must add those columns to the new ResultSource like so:
<pre>
$new_result_source->add_columns(
__PACKAGE__->add_columns(
"student",
{
data_type => "integer"
},
"first_name",
{
data_type => "text",
default_value => undef,
is_nullable => 1,
size => undef,
},
"last_name",
{
data_type => "text",
default_value => undef,
is_nullable => 1,
size => undef,
},
);
</pre>
In other words, to get all columns in the base table, make sure your raw SQL query has select table.* in it. And to get the columns that aren't in the base table add them to the new ResultSource via <em>add_columns</em>.
### Skip the view on ->deploy
## Register New ResultSource with the Schema
Make sure to add this code after '->add_columns' so that when you '->deploy' your schema, the view is skipped:
Next, we need to make our Schema aware of the new ResultSource.
<pre>
__PACKAGE__->result_source_instance->is_virtual(1);
</pre>
## Pass SQL to New ResultSource
The SQL to generate our virtual table (view) is just the raw SQL we'd use to perform the query[1].
<pre>
IRDA::Schema:->register_source( StudentSectionScoresbyAdmin' => $new_result_source );
__PACKAGE__->result_source_instance->view_definition(&lt;&lt;'SQL');
select
sec_scores.student student, first_name, last_name
from
roar.tbl_section_status as sec_status,
roar.tbl_section_scores as sec_scores,
people.tbl_students as students,
people.tbl_people as people
where
sec_status.section=sec_scores.section
and sec_status.student=sec_scores.student
and sec_scores.student=students.id
and students.person=people.id
and sec_status.status_type=5
and sec_scores.retest=0
and sec_scores.section in
(select id from assessment.tbl_sections as sections
where sections.admin = ?)
SQL
</pre>
## Use the Custom SQL Query
Finally, we can use the new ResultSource (from within a Catalyst application) as follows:
<pre>
my $student_section_scores_for_admin = [ $c->model('DB')
->resultset('StudentSectionScoresbyAdmin')->search ( {},
{
bind => [357]
}
) ];
</pre>
## Conclusion
By defining a custom ResultSource that uses a raw SQL query, one can get access to a genuine DBIC ResultSet.
By defining a virtual view that uses a raw SQL query, one can get access to a genuine DBIC ResultSet.
fn1. The raw SQL is treated as a subselect so the only change is to enclose it with parenthesis.