Variants

view: variants {
  sql_table_name: spree_variants ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: bin {
    sql: ${TABLE}.bin ;;
  }

  dimension: is_sold {
    type: yesno
    sql: (SELECT o.state in ('complete','resumed')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY created_at DESC
LIMIT 1) ;;
  }

  dimension: condition_description {
    sql: ${TABLE}.condition_description ;;
  }

  dimension: consigner_id {
    type: int
    hidden: yes
    sql: ${TABLE}.consigner_id ;;
  }

  dimension: cost_price {
    type: number
    sql: ${TABLE}.cost_price ;;
  }

  dimension: count_on_hand {
    type: int
    sql: ${TABLE}.count_on_hand ;;
  }

  dimension: depth {
    type: number
    sql: ${TABLE}.depth ;;
  }

  dimension: fabric_content {
    sql: ${TABLE}.fabric_content ;;
  }

  dimension: field_merchandiser_id {
    type: int
    sql: ${TABLE}.field_merchandiser_id ;;
  }

  dimension: foreign_size {
    sql: ${TABLE}.foreign_size ;;
  }

  dimension: has_discounts {
    type: yesno
    sql: ${TABLE}.has_discounts ;;
  }

  dimension: height {
    type: number
    sql: ${TABLE}.height ;;
  }

  dimension: hold_user_id {
    type: int
    sql: ${TABLE}.hold_user_id ;;
  }

  dimension: is_killed {
    type: yesno
    sql: ${TABLE}.is_killed ;;
  }

  dimension: is_master {
    type: yesno
    sql: ${TABLE}.is_master ;;
  }

  dimension: measurements {
    sql: ${TABLE}.measurements ;;
  }

  dimension: minimum_price {
    type: number
    sql: ${TABLE}.minimum_price ;;
  }

  dimension: notes {
    sql: ${TABLE}.notes ;;
  }

  dimension: original_price {
    type: number
    sql: ${TABLE}.original_price ;;
  }

  dimension: original_size {
    sql: ${TABLE}.original_size ;;
  }

  dimension: photos_available {
    type: yesno
    sql: ${TABLE}.photos_available ;;
  }

  dimension: position {
    type: int
    sql: ${TABLE}.position ;;
  }

  dimension: price {
    type: number
    sql: ${TABLE}.price ;;
  }

  dimension: product_id {
    type: int
    sql: ${TABLE}.product_id ;;
  }

  dimension: ready_for_sale {
    type: yesno
    sql: ${TABLE}.ready_for_sale ;;
  }

  dimension: sku {
    sql: ${TABLE}.sku ;;
  }

  dimension: check_price {
    type: yesno
    sql: ${TABLE}.check_price ;;
  }

  dimension: warehouse_id {
    type: int
    hidden: yes
    sql: ${TABLE}.warehouse_id ;;
  }

  dimension: weight {
    type: number
    sql: ${TABLE}.weight ;;
  }

  dimension: width {
    type: number
    sql: ${TABLE}.width ;;
  }

  dimension_group: born {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.born_date ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

  dimension_group: deleted {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.deleted_at ;;
  }

  dimension_group: killed {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.killed_at ;;
  }

  dimension_group: updated {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.updated_at ;;
  }

  measure: count {
    type: count
  }
}

- view: variants
  sql_table_name: spree_variants
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: bin
    sql: ${TABLE}.bin
  - dimension: is_sold
    type: yesno
    sql: (SELECT o.state in ('complete','resumed')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY created_at DESC
LIMIT 1)
  - dimension: condition_description
    sql: ${TABLE}.condition_description
  - dimension: consigner_id
    type: int
    hidden: true
    sql: ${TABLE}.consigner_id
  - dimension: cost_price
    type: number
    sql: ${TABLE}.cost_price
  - dimension: count_on_hand
    type: int
    sql: ${TABLE}.count_on_hand
  - dimension: depth
    type: number
    sql: ${TABLE}.depth
  - dimension: fabric_content
    sql: ${TABLE}.fabric_content
  - dimension: field_merchandiser_id
    type: int
    sql: ${TABLE}.field_merchandiser_id
  - dimension: foreign_size
    sql: ${TABLE}.foreign_size
  - dimension: has_discounts
    type: yesno
    sql: ${TABLE}.has_discounts
  - dimension: height
    type: number
    sql: ${TABLE}.height
  - dimension: hold_user_id
    type: int
    sql: ${TABLE}.hold_user_id
  - dimension: is_killed
    type: yesno
    sql: ${TABLE}.is_killed
  - dimension: is_master
    type: yesno
    sql: ${TABLE}.is_master
  - dimension: measurements
    sql: ${TABLE}.measurements
  - dimension: minimum_price
    type: number
    sql: ${TABLE}.minimum_price
  - dimension: notes
    sql: ${TABLE}.notes
  - dimension: original_price
    type: number
    sql: ${TABLE}.original_price
  - dimension: original_size
    sql: ${TABLE}.original_size
  - dimension: photos_available
    type: yesno
    sql: ${TABLE}.photos_available
  - dimension: position
    type: int
    sql: ${TABLE}.position
  - dimension: price
    type: number
    sql: ${TABLE}.price
  - dimension: product_id
    type: int
    sql: ${TABLE}.product_id
  - dimension: ready_for_sale
    type: yesno
    sql: ${TABLE}.ready_for_sale
  - dimension: sku
    sql: ${TABLE}.sku
  - dimension: check_price
    type: yesno
    sql: ${TABLE}.check_price
  - dimension: warehouse_id
    type: int
    hidden: true
    sql: ${TABLE}.warehouse_id
  - dimension: weight
    type: number
    sql: ${TABLE}.weight
  - dimension: width
    type: number
    sql: ${TABLE}.width
  - dimension_group: born
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.born_date
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: deleted
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.deleted_at
  - dimension_group: killed
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.killed_at
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - measure: count
    type: count