Re^2: [DBIX::Class] problem with Arbitrary SQL through a custom ResultSourceby selkovjr (Initiate)
|on Dec 20, 2007 at 18:02 UTC||Need Help??|
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:
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:
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:
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:
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:
In the caller: