Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Re^2: [DBIX::Class] problem with Arbitrary SQL through a custom ResultSource

by selkovjr (Initiate)
on Dec 20, 2007 at 18:02 UTC ( #658193=note: print w/replies, xml ) Need Help??

in reply to Re: [DBIX::Class] problem with Arbitrary SQL through a custom ResultSource
in thread [DBIX::Class] problem with Arbitrary SQL through a custom ResultSource

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/ model DB DBIC::Schema DB::Schema create=static + dbi:Pg:dbname=stat '' '' '{AutoCommit => 0}'

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

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;

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;

In the caller:

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

Replies are listed 'Best First'.
Re^3: [DBIX::Class] problem with Arbitrary SQL through a custom ResultSource
by Tommy (Chaplain) on Dec 16, 2009 at 16:51 UTC
    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
Re^3: [DBIX::Class] problem with Arbitrary SQL through a custom ResultSource
by Anonymous Monk on Mar 26, 2009 at 15:37 UTC
    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?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://658193]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2017-05-30 04:39 GMT
Find Nodes?
    Voting Booth?