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?

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.