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

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, { '<=', \"?" } ],
        },
    );
  };