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 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.

package My::Schema::Result::StudentSectionScoresbyAdmin;
use strict;
use warnings;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

__PACKAGE__->table('roar.tbl_section_scores');

The '->table' call must be present, but the value is not important.

Match new ResultSource Columns with Raw SQL Query Columns

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.

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

Skip the view on ->deploy

Make sure to add this code after '->add_columns' so that when you '->deploy' your schema, the view is skipped:

__PACKAGE__->result_source_instance->is_virtual(1);

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].

__PACKAGE__->result_source_instance->view_definition(<<'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

Use the Custom SQL Query

Finally, we can use the new ResultSource (from within a Catalyst application) as follows:

my $student_section_scores_for_admin = [ $c->model('DB')
  ->resultset('StudentSectionScoresbyAdmin')->search ( {}, 
{
    bind => [357]
}
) ];

Conclusion

By defining a virtual view that uses a raw SQL query, one can get access to a genuine DBIC ResultSet.