Hi, (This is my first perlmonk post so please go easy. And let me know what other details I can/should provide!)
I have a Catalyst project using DBIx. I'm trying to add a custom search/resultsource as per DBIx::Class manual here 
use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("Locations");
__PACKAGE__->add_columns(
  "locationid",
  { data_type => "integer", is_nullable => 0, size => undef },
  "name",
  { data_type => "string", is_nullable => 0, size => undef },
);
__PACKAGE__->set_primary_key("locationid");
__PACKAGE__->has_many(
  "npcs",
  "fhl::Schema::Npcs",
  { "foreign.locationid" => "self.locationid" },
);
__PACKAGE__->has_many(
  "incidents",
  "fhl::Schema::Incidents",
  { "foreign.locationid" => "self.locationid" },
);
...
my $source = __PACKAGE__->result_source_instance();
my $new_source = $source->new($source);
$new_source->source_name( 'DB::Locations::NumberActiveIncidents' );
$new_source->name( \<<SQL );
(
        SELECT l.*, count(i.incidentid) as NumIncidents
        FROM Incidents i, Locations l
        WHERE i.locationid = l.locationid AND i.enddatetime > ?
        GROUP BY l.locationid
        ORDER BY NumIncidents DESC 
)
SQL

fhl::Schema->register_source( 'DB::Locations::NumberActiveIncidents' =
+> $new_source );

[download]
This search works. Yes, I can probably achieve this without the custom result source thingo but that is beside the point (for now). But it returns the expected rows when I call 
my $active = [ $schema->resultset('Locations::NumberActiveIncidents')-
+>search( {}, { bind => [ $now ] } )];

[download]
An 'Incidents' row contains, among others, a 'LocationID' which is a row from the 'Locations' table above. Before adding the above code I could do, say, 
my $active = [$schema->resultset('Incidents::Active')->search({}, { bi
+nd => [ $now ] } )];
foreach my $incident (@$active) {
print $incident->description, " ", $incident->locationid->name, "\n";
    }

[download]
That is, I could access the locations row via the incidents locationid value. However, now when I try the above I get an error: 
Use of uninitialized value in subroutine entry at c:/Perl/site/lib/DBI
+x/Class/Storage/DBI.pm line 1003.
DBIx::Class::InflateColumn::get_inflated_column(): DBI Exception: DBD:
+:SQLite::db prepare_cached failed: unrecognized token: "0x2270f9c"(1)
+ at dbdimp.c line 271 [for Statement "INSERT INTO SCALAR(0x2270f9c) (
+locationid) VALUES (?)"] at (eval 970) line 6

[download]
The culprit line is: 
fhl::Schema->register_source( 'DB::Locations::NumberActiveIncidents' =
+> $new_source );

[download]
If I leave him out obviously the search does not work but the incident.locationid.name works. Please help. Any assistance suggestions would be great. Thanks, Sam

In reply to DBIx and custom ResultSource by sgundry

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.