nasty query

 SELECT "product"."sku", "product"."name", "product"."uri", "product"."price", "product"."short_description",
   CASE WHEN COUNT( variants.sku ) > 0 THEN 1 ELSE 0 END,
   CASE WHEN COUNT( variants.sku ) > 0 
     THEN
       CASE WHEN COALESCE( MIN( current_price_modifiers_2.price ), MIN( variants.price ) ) < MIN( variants.price )
         THEN COALESCE( MIN( current_price_modifiers_2.price ), MIN( variants.price ) )
         ELSE MIN( variants.price )
       END
     ELSE COALESCE( MIN( current_price_modifiers.price ), product.price )
   END AS selling_price,
   SUM( inventory.quantity + inventory_2.quantity ),
   ROUND( ( product.price - MIN( current_price_modifiers.price ) ) / product.price * 100 - 0.5 ),
   COALESCE( 
     CASE WHEN product.canonical_sku IS NULL THEN ROUND( AVG( message.rating ) *10 ) /10
          ELSE ROUND( AVG( message_2.rating ) *10 ) /10 END, 0
   ) AS average_rating
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" >= '2015-01-08 00:00:00+0000' )
  AND "current_price_modifiers"."quantity" <= '1'
  AND ( "current_price_modifiers"."roles_id" IS NULL OR "current_price_modifiers"."roles_id" IN (
        SELECT "roles"."roles_id" FROM "user_roles" "roles"  WHERE "roles"."users_id" = NULL
       ) )
  AND "current_price_modifiers"."sku" = "product"."sku"
  AND ( "current_price_modifiers"."start_date" IS NULL OR "current_price_modifiers"."start_date" <= '2015-01-08 00:00:00+0000' )
LEFT JOIN "product_reviews" "_product_reviews"
  ON "_product_reviews"."sku" = "product"."sku"
LEFT JOIN "messages" "message"
  ON "message"."messages_id" = "_product_reviews"."messages_id"
LEFT JOIN "inventories" "inventory"
  ON "inventory"."sku" = "product"."sku"
LEFT JOIN "products" "canonical"
  ON "canonical"."sku" = "product"."canonical_sku"
LEFT JOIN "product_reviews" "_product_reviews_2"
  ON "_product_reviews_2"."sku" = "canonical"."sku"
LEFT JOIN "messages" "message_2"
  ON "message_2"."messages_id" = "_product_reviews_2"."messages_id"
LEFT JOIN "products" "variants"
  ON "variants"."canonical_sku" = "product"."sku"
LEFT JOIN "price_modifiers" "current_price_modifiers_2"
  ON ( "current_price_modifiers_2"."end_date" IS NULL OR "current_price_modifiers_2"."end_date" >= '2015-01-08 00:00:00+0000' )
  AND "current_price_modifiers_2"."quantity" <= '1'
  AND ( "current_price_modifiers_2"."roles_id" IS NULL OR "current_price_modifiers_2"."roles_id" IN (
        SELECT "roles"."roles_id"
          FROM "user_roles" "roles"
        WHERE "roles"."users_id" = NULL
       ) )
  AND "current_price_modifiers_2"."sku" = "variants"."sku"
  AND ( "current_price_modifiers_2"."start_date" IS NULL OR "current_price_modifiers_2"."start_date" <= '2015-01-08 00:00:00+0000' )
LEFT JOIN "inventories" "inventory_2"
  ON "inventory_2"."sku" = "variants"."sku"
WHERE ( "message"."messages_id" IS NULL OR ( "message"."approved" = '1' AND "message"."public" = '1' ) )
  AND "me"."navigation_id" = '17' AND "product"."active" = '1'
GROUP BY "product"."sku", "product"."name", "product"."uri", "product"."price", "product"."short_description",
         "product"."canonical_sku"
ORDER BY "product"."priority" DESC

    $args->{quantity} = 1 unless defined $args->{quantity};

    my $schema = $self->result_source->schema;
    my $dtf = $schema->storage->datetime_parser;
    my $today = $dtf->format_datetime(DateTime->today);

    my @columns =
      map { "product.$_" } (qw/sku name uri price short_description/);

    return $self->search(
        {
            -or => [
                -and => [
                    'message.approved' => 1,
                    'message.public'   => 1,
                ],
                'message.messages_id' => undef
            ]
        },
        {
            alias   => 'product',
            columns => [ @columns ],
            '+columns' => [
                { has_variants => \"
                    CASE
                      WHEN COUNT(variants.sku) > 0 THEN 1
                      ELSE 0
                    END"
                },
                {
                    selling_price => \"
                    CASE
                      WHEN COUNT(variants.sku) > 0 THEN
                        CASE WHEN 
                          COALESCE(
                            MIN( current_price_modifiers_2.price ),
                            MIN( variants.price )
                          ) < MIN( variants.price )
                        THEN
                          COALESCE(
                            MIN( current_price_modifiers_2.price ),
                            MIN( variants.price )
                          )
                        ELSE
                          MIN( variants.price )
                        END
                      ELSE
                        COALESCE(
                          MIN( current_price_modifiers.price ), product.price
                        )
                    END AS selling_price"
                },
                {
                    inventory => \
                      "SUM(inventory.quantity + inventory_2.quantity)"
                },
                {
                    discount_percent => \"ROUND (
                    ( product.price - MIN( current_price_modifiers.price ) )
                    / product.price * 100 - 0.5 )"
                },
                {
                    average_rating => \"
                    COALESCE(
                      CASE
                        WHEN product.canonical_sku IS NULL THEN
                          ROUND(AVG( message.rating )*10)/10
                        ELSE
                          ROUND(AVG( message_2.rating )*10)/10
                      END,
                      0
                    ) AS average_rating"
                },
            ],
            join => [
                'current_price_modifiers',
                { _product_reviews => 'message' },
                'inventory',
                { canonical => { _product_reviews => 'message' } },
                { variants => [ 'current_price_modifiers', 'inventory', ], },
            ],
            bind => [
                [ end_date => $today ],
                [ quantity => $args->{quantity} ],
                [ { sqlt_datatype => "integer" } => $args->{users_id} ],
                [ start_date => $today ],
                [ end_date   => $today ],
                [ quantity   => $args->{quantity} ],
                [ { sqlt_datatype => "integer" } => $args->{users_id} ],
                [ start_date => $today ],
            ],
            group_by => [ @columns, 'inventory.quantity' ],
            result_class => 'DBIx::Class::ResultClass::HashRefInflator',
        }
    );

uses this complex join:

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

    my $subquery =
      $args->{self_resultsource}->schema->resultset('UserRole')
      ->search( { "roles.users_id" => { '=' => \"?" } }, { alias => 'roles' } )
      ->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, { '<=', \"?" } ],
        },
    );
  };