Class: AmazonSalesRankDataPoint

Inherits:
ApplicationRecord show all
Defined in:
app/models/amazon_sales_rank_data_point.rb

Overview

== Schema Information

Table name: amazon_sales_rank_data_points
Database name: primary

id :bigint not null, primary key
category_kind :string not null
category_name :string
observed_at :datetime not null
observed_on :date not null
product_category_id :string not null
rank :integer not null
source :string not null
source_batch_id :uuid
created_at :datetime not null
updated_at :datetime not null
catalog_item_id :bigint not null

Indexes

idx_amazon_sales_ranks_batch (source_batch_id) WHERE (source_batch_id IS NOT NULL)
idx_amazon_sales_ranks_item_history (catalog_item_id,observed_on)
idx_amazon_sales_ranks_unique_daily_point (catalog_item_id,category_kind,product_category_id,observed_on) UNIQUE

Foreign Keys

fk_rails_... (catalog_item_id => catalog_items.id) ON DELETE => cascade

One category-specific Amazon sales-rank observation for a listing and day.
Product Pricing and ANY_OFFER_CHANGED identify the category only by
ProductCategoryId; Catalog Items also supplies its title and whether it is a
classification browse node or a website display group.

Constant Summary collapse

CATEGORY_KINDS =

Category-kind values.

%w[browse_node display_group].freeze
SOURCES =

Source values.

%w[catalog_items product_pricing any_offer_changed].freeze

Constants included from Models::Schedulable

Models::Schedulable::SIMPLE_FORM_OPTIONS

Instance Attribute Summary collapse

Belongs to collapse

Class Method Summary collapse

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Models::Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Instance Attribute Details

#category_kind ⇒ String (readonly)

Returns:

  • (String) 


47
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 47

validates :category_kind, presence: true, inclusion: { in: CATEGORY_KINDS }

#observed_at ⇒ ActiveSupport::TimeWithZone (readonly)

Returns:

  • (ActiveSupport::TimeWithZone) 


51
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 51

validates :observed_at, presence: true

#observed_on ⇒ Date (readonly)

Returns:

  • (Date) 


49
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 49

validates :observed_on, presence: true

#product_category_id ⇒ String (readonly)

Returns:

  • (String)

Validations:

  • Uniqueness ({ scope: %i[catalog_item_id category_kind observed_on] }) 


45
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 45

validates :product_category_id, presence: true

#rank ⇒ Integer (readonly)

Returns:

  • (Integer) 


55
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 55

validates :rank, numericality: { only_integer: true, greater_than: 0 }

#source ⇒ String (readonly)

Returns:

  • (String) 


53
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 53

validates :source, presence: true, inclusion: { in: SOURCES }

Class Method Details

.browse_nodes ⇒ ActiveRecord::Relation<AmazonSalesRankDataPoint>

A relation of AmazonSalesRankDataPoints that are browse nodes. Active Record Scope

Returns:

See Also:



61
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 61

scope :browse_nodes, -> { where(category_kind: 'browse_node') }

.chronological ⇒ ActiveRecord::Relation<AmazonSalesRankDataPoint>

A relation of AmazonSalesRankDataPoints that are chronological. Active Record Scope

Returns:

See Also:



59
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 59

scope :chronological, -> { order(:observed_on, :observed_at) }

.display_groups ⇒ ActiveRecord::Relation<AmazonSalesRankDataPoint>

A relation of AmazonSalesRankDataPoints that are display groups. Active Record Scope

Returns:

See Also:



62
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 62

scope :display_groups, -> { where(category_kind: 'display_group') }

.recent ⇒ ActiveRecord::Relation<AmazonSalesRankDataPoint>

A relation of AmazonSalesRankDataPoints that are recent. Active Record Scope

Returns:

See Also:



60
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 60

scope :recent, ->(days = 90) { where(observed_on: days.days.ago.to_date..) }

.record_all!(catalog_item:, rankings:, observed_at:, source:, source_batch_id: nil) ⇒ Integer

Idempotently persist one payload's category ranks. A later observation for
the same listing/category/day wins; a delayed webhook cannot replace newer
data. source_batch_id makes a historical backfill independently undoable.

Parameters:

Returns:

  • (Integer) —

    inserted or updated row count



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 74

def self.record_all!(catalog_item:, rankings:, observed_at:, source:, source_batch_id: nil)
  return 0 if rankings.empty?

  observed_on = observed_at.in_time_zone('America/Chicago').to_date
  values = rankings.map do |ranking|
    sanitize_sql_array([
                         '(?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)',
                         catalog_item.id,
                         ranking.product_category_id,
                         ranking.category_kind,
                         ranking.category_name,
                         ranking.rank,
                         observed_on,
                         observed_at,
                         source,
                         source_batch_id
                       ])
  end.join(', ')

  with_connection do |connection|
    connection.execute(<<~SQL.squish).cmd_tuples
      INSERT INTO amazon_sales_rank_data_points
        (catalog_item_id, product_category_id, category_kind, category_name,
         rank, observed_on, observed_at, source, source_batch_id, created_at, updated_at)
      VALUES #{values}
      ON CONFLICT (catalog_item_id, category_kind, product_category_id, observed_on)
      DO UPDATE SET
        category_name = COALESCE(EXCLUDED.category_name, amazon_sales_rank_data_points.category_name),
        rank = EXCLUDED.rank,
        observed_at = EXCLUDED.observed_at,
        source = EXCLUDED.source,
        source_batch_id = EXCLUDED.source_batch_id,
        updated_at = CURRENT_TIMESTAMP
      WHERE amazon_sales_rank_data_points.observed_at <= EXCLUDED.observed_at
    SQL
  end
end

Instance Method Details

#catalog_item ⇒ CatalogItem

Returns:



42
 
# File 'app/models/amazon_sales_rank_data_point.rb', line 42

belongs_to :catalog_item