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



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.

        data_type     => "integer"
        data_type     => "text",
        default_value => undef,
        is_nullable   => 1,
        size          => undef,
        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:


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

sec_scores.student student, first_name, last_name

roar.tbl_section_status as sec_status,
roar.tbl_section_scores as sec_scores,
people.tbl_students as students,
people.tbl_people as people

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 = ?)

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]
) ];


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

My tags:
Popular tags:
  SQL arbitrary
Powered by Catalyst
Powered by MojoMojo Hosted by Shadowcat - Managed by Nordaaker