[Dbix-class] custom join condition weirdness

[Peter Mottram]

I'm working on a custom join condition for use in
https://metacpan.org/pod/Interchange6::Schema full reasons for which are
hard to explain - maybe we'll get to that in a later email if
explanation is needed.

I've added a new relationship to my Product class:

has_many
  current_price_modifiers => "Interchange6::Schema::Result::PriceModifier",
  sub {
    my $args = shift;

    my $subquery =
      $args->{self_resultsource}->schema->resultset('UserRole')
      ->search( { "me.users_id" => { '=' => \"?" } }
)->get_column('roles_id')
      ->as_query;

    return (
        {
            "$args->{foreign_alias}.sku" =>
              { -ident => "$args->{self_alias}.sku" },
            "$args->{foreign_alias}.end_date"   => [ undef, { '>=', \"?"
} ],
            "$args->{foreign_alias}.quantity"   => { '<=',  \"?" },
            "$args->{foreign_alias}.roles_id" =>
              [ undef, { -in => $subquery } ],
            "$args->{foreign_alias}.start_date" => [ undef, { '<=', \"?"
} ],
        },
    );
  };


This is consumed by a new method in ::ResultSet::Product:

sub with_selling_price {
    my ( $self, $args ) = @_;

    my $schema = $self->result_source->schema;

    if ( defined($args) ) {
        $self->throw_exception(
            "argument to with_price_modifiers must be a hash reference")
          unless ref($args) eq "HASH";
    }

    # we need a quantity
    $args->{quantity} = 1 unless defined $args->{quantity};

    my $dtf = $schema->storage->datetime_parser;
    my $today = $dtf->format_datetime(DateTime->today);
    my $rset = $self->search(
        undef,
        {
            join => "current_price_modifiers",
            bind => [
                [ end_date   => $today ],
                [ quantity   => $args->{quantity} ],
                [ { sqlt_datatype => "integer" } => $args->{users_id} ],
                [ start_date => $today ],
            ],
            '+select' => [ { min => "current_price_modifiers.price" } ],
            '+as'     => [ "selling_price" ],
            group_by => [ "product.sku" ],
        }
    );
    return $rset;
}


Via a relation with Navigation through NavigationProduct link table I
have a query thus:

            $products =
              $nav->navigation_products->search_related('product')
              ->with_selling_price( { quantity => 10, users_id => 1 } );


A few weird things happen:

$products->count is zero but if I iterate through this resultset I get
the expected 3 results.

Iterating through my products I would expect to be able to use
$product->get_column('selling_price') to find the min price from the
related current_price_modifiers relation but it is always undef.

Using DBIC_TRACE I see the resulting query:

SELECT "product"."sku", "product"."name", "product"."short_description",
"product"."description", "product"."price", "product"."uri",
"product"."weight", "product"."priority", "product"."gtin",
"product"."canonical_sku", "product"."active",
"product"."inventory_exempt", "product"."created",
"product"."last_modified", MIN( "current_price_modifiers"."price" ) FROM
"navigation_products" "me"  JOIN "products" "product" ON "product"."sku"
= "me"."sku" LEFT JOIN "price_modifiers" "current_price_modifiers" ON (
( "current_price_modifiers"."end_date" IS NULL OR
"current_price_modifiers"."end_date" >= ? ) AND
"current_price_modifiers"."quantity" <= ? AND (
"current_price_modifiers"."roles_id" IS NULL OR
"current_price_modifiers"."roles_id" IN ( SELECT "me"."roles_id" FROM
"user_roles" "me" WHERE ( "me"."users_id" = ? ) ) ) AND
"current_price_modifiers"."sku" = "product"."sku" AND (
"current_price_modifiers"."start_date" IS NULL OR
"current_price_modifiers"."start_date" <= ? ) ) WHERE (
"me"."navigation_id" = ? ) GROUP BY "product"."sku": '2014-12-01
00:00:00', '10', '1', '2014-12-01 00:00:00', '24'

In that query on the LHS I see MIN( "current_price_modifiers"."price" )
but the expected "AS selling_price" is missing - here is one clue but I
am completely baffled as to why.

The count problem is due to everything being collapsed so the bind
values no longer match:

SELECT COUNT( * ) FROM (SELECT "product"."sku" FROM
"navigation_products" "me"  JOIN "products" "product" ON "product"."sku"
= "me"."sku" WHERE ( "me"."navigation_id" = ? ) GROUP BY
"product"."sku") "product": '2014-12-01 00:00:00', '10', '1',
'2014-12-01 00:00:00', '24'

I can get around that by grabbing the count before applying
with_selling_price so that is not such a hardship (cheaper on the db
anyway) but shows my lack of understanding of custom joins I guess. Do I
need to return a second join condition in my relation to fix that?

Hoping someone can shed some light on the missing 'AS selling_price'.

TIA
PeteM