Sort by stock in spree commerce

product_scopes.rb

  add_search_scope :by_stock_location do
    order(%q{
      (
        SELECT 
          CASE 
          WHEN tt.count_on_hand > 0 
          THEN 2
          WHEN zz.backorderable = true
          THEN 1
          ELSE 0 
          END
        FROM (
          SELECT
            row_number() OVER (dpartition),
            z.id,
            bool_or(backorderable) OVER (dpartition) as backorderable
          FROM (
            SELECT DISTINCT ON (spree_variants.id) spree_products.id, spree_stock_items.backorderable as backorderable
            FROM spree_products
            JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."deleted_at" IS NULL 
            JOIN "spree_stock_items" ON "spree_stock_items"."variant_id" = "spree_variants"."id" AND "spree_stock_items"."deleted_at" IS NULL
            JOIN "spree_stock_locations" ON spree_stock_locations.id=spree_stock_items.stock_location_id
            WHERE spree_stock_locations.active = true
          ) z window dpartition as (PARTITION by id)
        ) zz 
        JOIN (
          SELECT
            row_number() OVER (dpartition),
            t.id,
            sum(count_on_hand) OVER (dpartition) as count_on_hand
          FROM (
            SELECT DISTINCT ON (spree_variants.id) spree_products.id, spree_stock_items.count_on_hand as count_on_hand
            FROM spree_products
            JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."deleted_at" IS NULL 
            JOIN "spree_stock_items" ON "spree_stock_items"."variant_id" = "spree_variants"."id" AND "spree_stock_items"."deleted_at" IS NULL
          ) t window dpartition as (PARTITION by id)
        ) tt ON tt.row_number = 1 AND tt.id = spree_products.id
        WHERE zz.row_number = 1 AND zz.id=spree_products.id
      ) DESC, available_on DESC
    })
  end