schema.rb

# encoding: UTF-8
# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# Note that this schema.rb definition is the authoritative source for your
# database schema. If you need to create the application database on another
# system, you should be using db:schema:load, not running all the migrations
# from scratch. The latter is a flawed and unsustainable approach (the more migrations
# you'll amass, the slower it'll run and the greater likelihood for issues).
#
# It's strongly recommended that you check this file into your version control system.

ActiveRecord::Schema.define(version: 20160419113500) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "accounts", force: :cascade do |t|
    t.string   "name",             null: false
    t.text     "website"
    t.string   "vat_number"
    t.string   "company_number"
    t.string   "email_address"
    t.string   "phone_number"
    t.string   "address_1"
    t.string   "address_2"
    t.string   "address_3"
    t.string   "address_city"
    t.string   "address_county"
    t.string   "address_country"
    t.string   "address_postcode"
    t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
  end

  create_table "accounts_competitors", id: false, force: :cascade do |t|
    t.integer "account_id"
    t.integer "competitor_id"
  end

  add_index "accounts_competitors", ["account_id", "competitor_id"], name: "index_accounts_competitors_on_account_id_and_competitor_id", using: :btree
  add_index "accounts_competitors", ["account_id"], name: "index_accounts_competitors_on_account_id", using: :btree
  add_index "accounts_competitors", ["competitor_id"], name: "index_accounts_competitors_on_competitor_id", using: :btree

  create_table "alerts", force: :cascade do |t|
    t.datetime "time"
    t.string   "header"
    t.string   "info"
    t.boolean  "is_dismissed"
    t.integer  "account_id"
    t.datetime "created_at",   null: false
    t.datetime "updated_at",   null: false
  end

  add_index "alerts", ["account_id"], name: "index_alerts_on_account_id", using: :btree

  create_table "categories", force: :cascade do |t|
    t.text     "name",              null: false
    t.integer  "account_id"
    t.integer  "category_group_id"
    t.datetime "created_at",        null: false
    t.datetime "updated_at",        null: false
  end

  add_index "categories", ["account_id"], name: "index_categories_on_account_id", using: :btree
  add_index "categories", ["category_group_id"], name: "index_categories_on_category_group_id", using: :btree

  create_table "category_groups", force: :cascade do |t|
    t.text     "name"
    t.integer  "account_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_index "category_groups", ["account_id"], name: "index_category_groups_on_account_id", using: :btree

  create_table "comparisons", id: false, force: :cascade do |t|
    t.integer "account_variant_id"
    t.integer "competitor_variant_id"
  end

  add_index "comparisons", ["account_variant_id", "competitor_variant_id"], name: "index_comparisons", using: :btree

  create_table "competitors", force: :cascade do |t|
    t.string   "name",       null: false
    t.string   "website",    null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_index "competitors", ["website"], name: "index_competitors_on_website", using: :btree

  create_table "crawls", force: :cascade do |t|
    t.string   "_id"
    t.string   "act_id"
    t.datetime "started_at"
    t.datetime "finished_at"
    t.string   "status"
    t.text     "status_message"
    t.integer  "downloaded_bytes"
    t.integer  "pages_in_queue"
    t.integer  "pages_crawled"
    t.integer  "pages_outputted"
    t.integer  "pages_failed"
    t.text     "details_url"
    t.text     "results_url"
    t.boolean  "imported",         default: false
  end

  create_table "imports", force: :cascade do |t|
    t.text     "message"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "notifications", force: :cascade do |t|
    t.text     "name"
    t.text     "description"
    t.integer  "account_id"
    t.integer  "trigger_id"
    t.boolean  "is_unread"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "notifications", ["account_id"], name: "index_notifications_on_account_id", using: :btree
  add_index "notifications", ["trigger_id"], name: "index_notifications_on_trigger_id", using: :btree

  create_table "offers", force: :cascade do |t|
    t.decimal  "price",                            null: false
    t.boolean  "sale",             default: false
    t.decimal  "compare_at_price"
    t.json     "price_breaks"
    t.integer  "variant_id",                       null: false
    t.datetime "created_at",                       null: false
    t.datetime "updated_at",                       null: false
  end

  add_index "offers", ["price"], name: "index_offers_on_price", using: :btree
  add_index "offers", ["sale"], name: "index_offers_on_sale", using: :btree
  add_index "offers", ["variant_id"], name: "index_offers_on_variant_id", using: :btree

  create_table "products", force: :cascade do |t|
    t.text     "name",        null: false
    t.integer  "category_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "products", ["category_id"], name: "index_products_on_category_id", using: :btree

  create_table "triggers", force: :cascade do |t|
    t.integer  "threshold"
    t.integer  "time_range"
    t.integer  "account_id"
    t.integer  "category_id"
    t.integer  "product_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "triggers", ["account_id"], name: "index_triggers_on_account_id", using: :btree
  add_index "triggers", ["category_id"], name: "index_triggers_on_category_id", using: :btree
  add_index "triggers", ["product_id"], name: "index_triggers_on_product_id", using: :btree

  create_table "users", force: :cascade do |t|
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "role"
    t.integer  "account_id"
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.string   "first_name"
    t.string   "last_name"
    t.integer  "user_type",              default: 3
    t.datetime "created_at",                          null: false
    t.datetime "updated_at",                          null: false
  end

  add_index "users", ["account_id"], name: "index_users_on_account_id", using: :btree
  add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree
  add_index "users", ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true, using: :btree
  add_index "users", ["user_type"], name: "index_users_on_user_type", using: :btree

  create_table "variants", force: :cascade do |t|
    t.text     "name"
    t.text     "sku"
    t.text     "url"
    t.text     "image_url"
    t.decimal  "price",       null: false
    t.integer  "parent_id"
    t.string   "parent_type"
    t.integer  "import_id"
    t.integer  "category_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "variants", ["category_id"], name: "index_variants_on_category_id", using: :btree
  add_index "variants", ["import_id"], name: "index_variants_on_import_id", using: :btree
  add_index "variants", ["parent_id"], name: "index_variants_on_parent_id", using: :btree
  add_index "variants", ["price"], name: "index_variants_on_price", using: :btree
  add_index "variants", ["sku"], name: "index_variants_on_sku", using: :btree
  add_index "variants", ["url"], name: "index_variants_on_url", using: :btree

end

variant.rb

class Variant < ApplicationRecord

  has_and_belongs_to_many :comparisons,
    class_name: "Variant",
    foreign_key: "competitor_variant_id",
    association_foreign_key: "account_variant_id",
    join_table: "comparisons"

  has_many :offers
  belongs_to :category

  belongs_to :parent, polymorphic: true
  
  def self.min
    minimum(:price)
  end

  scope :lowest, -> { joins(:comparisons).group("variants.id").having("MIN(comparisons_variants.price) > variants.price") }
  
end

`SELECT * FROM spree_variants
JOIN spree_products ON spree_variants.product_id = spree_products.id
GROUP BY spree_variants.id, spree_products.id
HAVING spree_products.wholesale_price < MIN(spree_variants.cost_price)

SELECT * FROM spree_variants
JOIN spree_products ON spree_variants.product_id = spree_products.id
WHERE spree_products.wholesale_price <
(SELECT MIN(spree_variants.cost_price) FROM spree_variants WHERE spree_products.id = spree_variants.product_id)
`

scope :lowest -> { joins('JOIN comparisons ON account_variant_id = variants.id').joins('JOIN variants AS competition_variants ON competitor_variant_id = competitor_variants.id').group_by('competitor_variants.id, variants.id').having('variants.price < MIN(competitor_variants.price') }