[Dbix-class] Setting view_definition at runtime

[Frank Wiegand]

Hi @,

I'm trying to fetch some data via custom SQL, so I set this up
according to DBIx::Class::Manual::Cookbook:

package MyApp::Schema::Result::Search;

        use strict;
        use warnings;

        use parent 'DBIx::Class::Core';

        __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
        __PACKAGE__->table('null');
        __PACKAGE__->add_columns(
            'id'       => { data_type => 'text' },
            'title'    => { data_type => 'text' },
            'headline' => { data_type => 'text' },
            'rank'     => { data_type => 'text' },
        );

        __PACKAGE__->result_source_instance->is_virtual(1);

        # ---> this is just template (simplified for this posting)
        my $sql = <<'EOT';
            SELECT id, title, ts_headline(content, query,
'MaxFragments=2') headline, rank
              FROM (
                SELECT id, title, content, ts_rank_cd(tsv, query) rank, query
                  FROM article, %s('german', ?) query
                 WHERE query @@ tsv
              ORDER BY rank DESC
            ) AS xx
        EOT
        # <----

        __PACKAGE__->result_source_instance->view_definition( $sql );

        1;

I'm using PostgreSQL, and a would like to prepare the view_definition
at runtime:

# test query if it fits to to_tsquery ($search is the query string)
        my $q = $dbh->quote($search);
        my $query_func = 'to_tsquery';
        eval { $dbh->do( "SELECT to_tsquery($q)" ); 1 };
        $query_func = 'plainto_tsquery' if $@;

        # expand the view_definition SQL
        $c->model('MyApp::Search')->result_source->view_definition(
            sprintf
$c->model('MyApp::Search')->result_source->view_definition,
$query_func
        );

This works fine for the first run, but then the view_definition value
does not change:

# no calls to $c->model('MyApp::Search') till here

my $rsource = $c->model('MyApp::Search')->result_source;
        print STDERR $rsource->view_definition;
        # prints the mentioned template above

        $rsource->view_definition( sprintf $rsource->view_definition,
'to_tsquery' );
        print STDERR $rsource->view_definition;
        # prints the expanded template (%s => to_tsquery) [*]

        $rsource->view_definition( sprintf $rsource->view_definition,
'plainto_tsquery' );
        print STDERR $rsource->view_definition;
        # prints the same as [*]

So it looks like the value for view_definition gets cached. And I
would like to turn off this behaviour. What's the recommended way to
do this?


Thanks, Frank