Morning Mighty Monks ... I had my morning coffee already, but it does not seem to have kicked in yet. I'm struggling with a complex query that I would like to turn into a custom result set and no matter how much I twiddle, the results don't match my expectations.

In the beginning there was a mysql table, which contained a "POINT" column. Said column wanted to be matched with user entered information to compute a distance and return the a specified number of rows, where (geo-) distance between the user data and the column was smallest.

Using information found in the DBIx::Class cookbook, I came up with this in my Schema class:

package MyApp::Location;

use base qw/DBIx::Class/;
...
__PACKAGE__->add_columns(
...
    location => {
        data_type => 'point',
    },

);
...
# custom result set
my $source = __PACKAGE__->result_source_instance();
my $new_source = $source->new( $source );
$new_source->source_name( 'ClosestUsers' );
$new_source->name( \qq{
( SELECT c.*,
    GLength(LineStringFromWKB(
       LineString(AsBinary(c.location), AsBinary(?))))
  AS distance
  FROM loc c 
  ORDER by distance asc
  LIMIT ?)
});
MyApp->register_source( 'ClosestUsers' => $new_source );
...
[download]

Then further on in the code, I attempt to use said custom result set via (under Catalyst):

my $closeBy = [
  $c->model('MyApp')->resultset('ClosestUsers' )
   ->search( {}, 
        {
            bind  => [ $somelocation,
                       $numberOfUsers]
        }
        )
   ];
[download]

Using DBIC_TRACE I can see the query flying by and it does not seem to match what I expected:

SELECT me.id_checkin, me.user_id, me.modified, 
       me.location, me.accuracy FROM 
( SELECT c.*, 
      GLength(LineStringFromWKB(
      LineString(AsBinary(c.location),AsBinary(?))))
  AS distance
  FROM loc c
  ORDER by distance asc
  LIMIT ?)
[download]

I want to get rid of that outer select and be able to have the "distance" in my ResultSet.

Any words of wisdom a monk could provide on this topic?