[Dbix-class] DBIx and Arbitrary SQL through a custom ResultSource

[Vangelis Katsikaros]

Hi Wolfgang

On 01/21/2012 02:46 PM, Wolfgang Kinkeldei wrote:
> Hi Vangelis,
>
> Am 21.01.2012 um 11:15 schrieb Vangelis Katsikaros:
>
>> Hello
>>
>> I had a problem following the instructions in "Arbitrary SQL through a custom ResultSource" [1] since it gave me an error of `Can't locate object method "result_source_instance"`
>>
>> I found a solution in this perlmonk answer [2]. Indeed this works:
>>
>> <code>---------------------------
>>
>> package Dir::Dir::ComplexQuery;
>>
>> use strict;
>> use base 'DBIx::Class::Core';
>>
>> __PACKAGE__->table( 'dummy_table_name' );
>> __PACKAGE__->add_columns( qw/col1 col2 col3/ );
>> __PACKAGE__->result_source_instance->name( \q!(complex SQL ... )! );
>>
>> </code>---------------------------
>
> you referred to an answer using a deprecated syntax. There is a note on this in the Cookbook-pod [1] on this. Instead you should follow the instructions in the Cookbook example.
>
> This Works:
>
> <code>
> package Dir::Dir::ComplexQuery;
> use strict;
> use warnings;
> use base 'DBIx::Class::Core';
>
> __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
> __PACKAGE__->table( 'dummy_table_name' );
> __PACKAGE__->add_columns( qw/col1 col2 col3/ );
> __PACKAGE__->result_source_instance->is_virtual(1);
> __PACKAGE__->result_source_instance->view_definition(q[
>    complex SQL ...
> ]);
>
> 1;
> </code>
>
> maybe you forgot the `__PACKAGE__->table()` method call. At least I can get the message you wrote when omitting this call.

You are correct. The above example works fine and I updated my code.

I probably forgot to load something - maybe it's becuase I had never 
created the "class table" myself. May I suggest, a tiny addition to the 
documentation:

diff Cookbook.pod Cookbook_copy.pod
120c120,123
<   # ->table, ->add_columns, etc.
---
 >   # ->table, ->add_columns, etc. Minimal example:
 >   __PACKAGE__->table( 'dummy_table_name' );
 >   __PACKAGE__->add_columns( qw/col1 col2 col3/ );
 >

After seeing Peter's reply regarding the changes of in the next 
releases, this might not be a valid change afterall.

>
>>
>> and  I can normally do:
>> ...->resultset('ComplexQuery')->search();
>>
>> My questions are the following:
>>
>> 1. The function "table" is the one from DBIx::Class::ResultSourceProxy::Table as described in [3], right? I am using `DBIx::Class::Schema::Loader` so I never had created a DBIx "class table" myself.
>
> ResultSource::View tables are defined by you and will not be touched or overwritten by DBIx::Class::Schema::Loader, as long as there are no naming conflicts.
>
>> 2. The "__PACKAGE__->table('table_name')" will NOT trigger a creation of this table (in this case it's a view) to the actual DB, correct?
>> Or is there a scenario where the creation of a view hac be triggered and I can end up with a view named 'dummy_table_name' in my DB?
>
> by calling `__PACKAGE__->result_source_instance->is_virtual(1);` you ensure that nothing will happen in case you would try to deploy a schema to your database. You will find this as a comment in the code snippet in [1].
>
>> 3. In which DBIx package is "result_source_instance" defined? I did searched but I couldn't find it - maybe I am not searching the right way.
>
> I found this method in DBIx::Classs::DB, but for using the module you should not need to know the location of this method.
>

Thanks for all the answers!
Vangelis