new interchange6 product listing query

a thing of beauty

SELECT "product"."sku", "product"."name", "product"."uri", "product"."price", 
       "product"."short_description", (
    SELECT COUNT( * )
      FROM "products" "variants_alias"
    WHERE "variants_alias"."canonical_sku" = "product"."sku"
   ), COALESCE( (
        SELECT AVG( "message"."rating" )
          FROM "products" "canonical_alias"
          JOIN "product_reviews" "_product_reviews"
            ON "_product_reviews"."sku" = "canonical_alias"."sku"
          JOIN "messages" "message"
            ON "message"."messages_id" = "_product_reviews"."messages_id"
        WHERE "canonical_alias"."sku" = "product"."canonical_sku"
          AND "message"."approved" = '1'
          AND "message"."public" = '1'
       ), (
        SELECT AVG( "message"."rating" )
          FROM "product_reviews" "_product_reviews_alias"
          JOIN "messages" "message"
            ON "message"."messages_id" = "_product_reviews_alias"."messages_id"
        WHERE "_product_reviews_alias"."sku" = "product"."sku"
          AND "message"."approved" = '1'
          AND "message"."public" = '1'
       ) ) AS "average_rating",
       COALESCE( LEAST( COALESCE( (
                SELECT MIN( "price_modifiers"."price" )
                  FROM "products" "variants_alias"
                  JOIN "price_modifiers" "price_modifiers"
                    ON "price_modifiers"."sku" = "variants_alias"."sku"
                WHERE ( "end_date" IS NULL OR "end_date" >= '2015-01-29 00:00:00+0000' )
                  AND "quantity" <= '1' AND "roles_id" IS NULL
                  AND ( "start_date" IS NULL OR "start_date" <= '2015-01-29 00:00:00+0000' )
                  AND "variants_alias"."canonical_sku" = "product"."sku"
               ), (
                SELECT MIN( "variants_alias"."price" )
                  FROM "products" "variants_alias"
                WHERE "variants_alias"."canonical_sku" = "product"."sku"
               ) ), (
            SELECT MIN( "variants_alias"."price" )
              FROM "products" "variants_alias"
            WHERE "variants_alias"."canonical_sku" = "product"."sku"
           ) ), COALESCE( (
            SELECT MIN( "price_modifiers_alias"."price" )
              FROM "price_modifiers" "price_modifiers_alias"
            WHERE ( "end_date" IS NULL OR "end_date" >= '2015-01-29 00:00:00+0000' )
             AND "price_modifiers_alias"."sku" = "product"."sku"
             AND "quantity" <= '1' AND "roles_id" IS NULL
             AND ( "start_date" IS NULL OR "start_date" <= '2015-01-29 00:00:00+0000' )
           ), "product"."price" ) ) AS "selling_price", COALESCE( (
        SELECT SUM( "inventory"."quantity" )
          FROM "products" "variants_alias"
          JOIN "inventories" "inventory"
            ON "inventory"."sku" = "variants_alias"."sku"
        WHERE "variants_alias"."canonical_sku" = "product"."sku"
       ), (
        SELECT "inventory_alias"."quantity"
          FROM "inventories" "inventory_alias"
        WHERE "inventory_alias"."sku" = "product"."sku"
       ) ) AS "quantity_in_stock"
  FROM "navigation_products" "me"
  JOIN "products" "product"
    ON "product"."sku" = "me"."sku"
WHERE "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
  LIMIT '40'