Perhaps a simpler example will help.

You can set __PACKAGE__->result_source_instance->name to any, arbitrary SQL your DB backend understands. You need to pass it as a scalar reference and the enclosing parens are important.

Running the following code with DBIC_TRACE=1 to display the generated SQL shows:

SELECT me.number FROM (select 10*10 as number) me: 
100

#!/usr/bin/perl
use strict;
use warnings;

package MySchema::ArbitrarySQL;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table('dummy');
__PACKAGE__->add_columns(qw/number/);
__PACKAGE__->result_source_instance->name(\'(select 10*10 as number)');


package MySchema;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->register_class(ArbitrarySQL => 'MySchema::ArbitrarySQL');


package main;

my $schema = MySchema->connect(
    'dbi:SQLite:dbname=myschema.db', undef, undef,
);

my ($r) = $schema->resultset('ArbitrarySQL')->all;

print $r->number, "\n";

I have a version of this example for those who create their models with Catalyst and DBIC::Schema. The idea is the same as in the original Cookbook example quoted by dreel, as well as in semifor's version: you need to give your query's result the appearance of a database table.

The problem with these examples is that you can't use them verbatim. Different transformations may be necessary, depending on how the rest of your code is arranged. It took me a wee whiley to figure out how to make it work with DBIC::Schema, in which case it simply meant throwing a few things away.

I used this command to create my model and schema:

  script/stat_create.pl model DB DBIC::Schema DB::Schema create=static
+ dbi:Pg:dbname=stat '' '' '{AutoCommit => 0}'
[download]

Here, DB is the name of my model, DB::Schema is the name of the generated schema class serving as the interface to the model's data; stat is the name of my application (so the script is named stat_create.pl), and the postgres database containing the data for the model is also named stat, which is just a co-incidence — it doesn't have to be. These are the things that vary, and how you name them is totally up to you. There is no magic in names. With things named as they are, running this command results in the following directory structure:

  |-- lib
  |   |-- DB
  |   |   |-- Schema
  |   |   |   |-- Class1.pm
  |   |   |   |-- Class2.pm
  |   |   |   |-- ...
  |   |   |   `-- ClassN.pm
  |   |   `-- Schema.pm
[download]

Class1 .. ClassN is my depiction of the myriad of classes that the Catalyst helper script creates after it examines the existing database. Each class corresponds to a table. If, instead of building your Catalyst application around the existing database, you choose to create it with Catalyst, you will have arrived here by a different route, but this example will still be valid, as long as your custom query module is in the same parental namespace and is kept with the rest of the schema classes. Creation of schema classes — automatic or manual — is adequately explained in the Catalyst Tutorial.

Now, suppose you want to run a query that is neither a simple select on a table, nor can be expressed in terms of relationships supported by DBIx::Class. I need this kind of thing, for example, to calculate an aggregate over a complicated cascade of joins with non-trivial join conditions. As in semifor's example, let's say you want the database to add two numbers for you.

Create the module lib/DB/Schema/Add.pm with the following in it:

package DB::Schema::Add;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("NONE");
__PACKAGE__->add_columns(qw/number/);
__PACKAGE__->result_source_instance
  ->name(\'(select 10*10 as number)');

1;
[download]

Done. If you have created the new class in the same directory with other table classes for this model's schema, it will be registered automatically.

Use it anywhere in Catalyst (controllers, models, &c.), as:

  my ($res) = $c->model('Add')->all;
  my $number = $res->number;
[download]

That's all. Note once again that in this example, "DB", "DB::Schema", "Add", "number", and "NONE" are just arbitrary names. None of them has a special meaning to Catalyst or DBIx::Class, so you can use any names that make sense to you; just make sure that if you have previously created your model's schema as DB::Schema, your custom query container is named DB::Schema::CustomQuery, or something like that.

Passing a parameter

For completeness, and to make the example more interesting, here's one way to pass parameters into the query (it will be prepared/executed inside DBIx):

In your model:

package DB::Schema::Add;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("NONE");
__PACKAGE__->add_columns(qw/number/);
__PACKAGE__->result_source_instance
  ->name(\'(select ?::integer * ?::integer as number)');

1;
[download]

In the caller:

  my ($res) = $c->model('Add')->search({}, {bind => [5, 7]});
  my $body = $res->number;
[download]

This should have been upvoted much more. This was exactly the answer I needed for Catalyst. Selkovjr, if you are still around perl monks from time to time, Please contact me so I can personally express my thanks for this invaluable contribution. My email address is $ echo YWNlQHRvbW15YnV0bGVyLm1lCg==|base -d
--
Tommy

But isn't this still creating a select statement?

What if i simply want to exec a stored procedure, per dreel?

Am I not understanding something?

MS SQL Server do not understand this syntax: Select from sp_name and queries with SELECT FROM (EXEC sp_name) me: are wrong ( That's why now i'm using simple DBI prepare, exec to call procedures.