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

[Wolfgang Kinkeldei]

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.

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

> [1] http://search.cpan.org/~arodland/DBIx-Class-0.08196/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource
> [2] http://www.perlmonks.org/?node_id=658193
> [3] http://search.cpan.org/~arodland/DBIx-Class-0.08196/lib/DBIx/Class/ResultSource.pm#DESCRIPTION


[...]

Best,

Wolfgang

-- 

' /\_/\ ' .print[split//,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - wolfgang at kinkeldei dot de -->