Hot questions for Using Spree in postgresql

Question:

I am customizing a Spree 2.3 application in Rails 4. When I save a price.amount, it appears to save correctly in the database, but when I retrieve it, it is wrapped in the BigDecimal class and returns 0.

How can I store, or retrieve, the correct value?

# in the form
<%= number_field_tag :amount %>

# controller action
@variant.price = params[:amount]

# appears in PostgresQL database as type 'numeric'
id: 35, amount: 60.00

# retrieved in Rails console as BigDecimal class instance
# looks like the wrong amount
2.1.1 :001 > Spree::Price.find_by_id(35).amount
=> #<BigDecimal:105806d20,'0.0',9(27)>

# converts to 0.0
2.1.1 :002 > Spree::Price.find_by_id(35).amount.to_f
=> 0.0

# testing data retrieval in the console
2.1.1 :003 > ActiveRecord::Base.connection.execute("SELECT * FROM spree_prices WHERE id=35").first
=> {"id"=>"35", "variant_id"=>"35", "amount"=>"60.00", "currency"=>"USD", "deleted_at"=>nil} 
2.1.1 :004 > Spree::Price.find(35)
=>  #<Spree::Price id: 35, variant_id: 35, amount: #<BigDecimal:109ec4a28,'0.0',9(27)>, currency: "USD", deleted_at: nil>

Answer:

The problem was in a typo in the price model decorator. The pure PostgresQL doesn't trigger the callback, but using the 'find' method does. This accounts for the seemingly impossible results above.

#original price_decorator.rb
after_initialize :set_defaults

def set_defaults
  self.amount = 0.0
end

#corrected price_decorator.rb
after_initialize :set_defaults

def set_defaults
  self.amount ||= 0.0
end

Question:

I want to combine results on my postgres query, at the product properties. Currently it is giving me these results:

name        id   value                  sku     item_count
Item # 1    3    Item                   IT-EM1  3
Item # 1    2    006058465456           IT-EM1  3
Item # 2    3    Item                   IT-EM2  1
Item # 2    2    055045004505           IT-EM2  1

I would like it to return the following:

name       id#1   value#1    id#2    value#2         sku       item_count
Item # 1   3      Item       2       006058465456    IT-EM1    3
Item # 2   3      Item       2       055045004505    IT-EM2    1

The id is the product property id (2 being GTIN and 3 being Brand,) the value is the value of that particular product property. My query is below:

SELECT
    p.name,
    l.property_id AS id,
    l.value AS value,
    v.sku,
    s.count_on_hand AS item_count,
FROM
    spree_variants v INNER JOIN
    spree_products p ON v.product_id = p.id LEFT OUTER JOIN
    spree_stock_items s ON v.id = s.variant_id INNER JOIN
    spree_product_properties l ON l.product_id = p.id
WHERE
    s.count_on_hand > 0

Any ideas?


Answer:

Answered this myself. Maybe not the most elegant soltuion, but:

SELECT
    p.name,
    l.property_id AS id,
    l.value AS value,
    li.property_id AS id_two,
    li.value AS value_two,
    v.sku,
    s.count_on_hand AS item_count,
FROM
    spree_variants v INNER JOIN
    spree_products p ON v.product_id = p.id LEFT OUTER JOIN
    spree_stock_items s ON v.id = s.variant_id INNER JOIN
    spree_product_properties l ON l.product_id = p.id INNER JOIN
    spree_product_properties li ON li.product_id = p.id
WHERE
    s.count_on_hand > 0 AND
    l.property_id = 2 AND
    li.property_id = 3

Add the product properties column again as a different variable (l and li) then define in WHERE that l.property_id = 2 and li.property_id = 3

Question:

I'm trying to add a friendly_id slug to an existing Rails model, Spree::Variant. Variant belongs_to Product, which already has its own slugs that are working fine (Spree and these models are from a 3rd party gem).

When I try saving a Variant I'm expecting a slug to be generated and saved to the slug field in the database, but the field remains NULL.

Here's my model code:

Spree::Variant.class_eval do
  extend FriendlyId
  friendly_id :slug_candidates, :use => :slugged
  validates :slug, length: { minimum: 3 }, allow_blank: true, uniqueness: true

  self.whitelisted_ransackable_attributes = %w[weight sku slug]

  def parent_slug
    self.product.slug
  end

  def option_value_list
    if !self.option_values.nil? && self.option_values.length > 0
      opts = self.option_values.collect{ |v| 
      v.presentation }.join('-')
      return opts
    else
      return ''
    end
  end

  def slug_candidates
    [
      [:parent_slug, :option_value_list],
      [:parent_slug, :option_value_list, :sku]
    ]
  end

  def should_generate_new_friendly_id?
    puts "Is new #{new_record?}, is blank #{slug.blank?}" #Is new false, is blank true when updating test record
    new_record? || slug.blank?
  end

end

Similar questions are answered with making sure should_generate_new_friendly_id? is defined correctly, which it appears to be here. I've verified that it gets called and return True when I update a record, yet the record still doesn't end up with a slug.

I've also verified that option_value_list is called, and outputs the desired information. An example return value from my app is "Titanium".

Why isn't my slug created? Where can I look to narrow the problem further?


Answer:

I can't really say I know the solution, but to narrow down the problem, you need to check the Spree::Variant source code which delegates the slug to the product table.

On the product table, the slug is a has_many relationship, known by the use: :history. (Not related to the problem)

The problem is with the Spree::Core::DelegateBelongsTo#(delegator_for_setter|| delegator_for) methods, which are returning early if the column name exists on the table, rather than reading or updating the records accordingly.

A way to work around these(as I don't know if Spree provides a better approach for handling these) are to redefine your slug methods as such:

def slug
  read_attribute(:slug)
end

def slug=(new_slug)
  write_attribute(:slug, new_slug)
end

Hope I was able to help.

Question:

I have a question regarding pg_search: I installed the gem on solidus 2.9 and added a product_decorator.rb model like this:

Spree::Product.class_eval do
  include PgSearch::Model

  pg_search_scope :search, against: [:name, :description, :meta_description, :meta_keywords],
    using: {tsearch: {dictionary: "english"}}

end

It works fine in rails console.

How can I make it work when I use the search field in solidus' frontend? I tried adding it to the product controller but can't seem to make it work. Thanks!

UPDATE

So after kennyadsl's comments I have this:

#lib/mystore/products_search.rb

module MyStore
  class ProductSearch < Spree::Core::Search::Base

    def retrieve_products
      Spree::Product.pg_search
    end
  end
end
#models/spree/product_decorator.rb

Spree::Product.class_eval do
  include PgSearch::Model

  pg_search_scope :search, against: [:name, :description, :meta_description, :meta_keywords], using: {tsearch: {dictionary: "english"}}

  def self.text_search(keywords)
        if keywords.present?
      search(keywords)
    else
      Spree::Product.all
    end
  end       

end
#controllers/products_controller.rb

def index
  @searcher = build_searcher(params.merge(include_images: true))
  @products = @searcher.retrieve_products(params)
end

Answer:

By default, products search is performed through the Spree::Core::Search::Base class, but it's configurable so you can create your own class that inherits from that one:

module Spree
  module Core
    module Search
      class PgSearch < Spree::Core::Search::Base
        def retrieve_products
          PgSearch.multisearch(...)
        end
      end
    end
  end
end

To see what's available in that class you can refer to the original implementation here:

https://github.com/solidusio/solidus/blob/eff22e65691a64d2ce0bf8a919d8456010360753/core/lib/spree/core/search/base.rb

Once you've added your own logic, you can use the new class to perform search by adding this line into a config/initializers/spree.rb:

Spree::Config.searcher_class = Spree::Core::Search::PgSearch

UPDATE

After some back and forth with Ignacio, this is a working version to perform a basic search with PgSearch in the Solidus storefront:

# app/models/spree/product_decorator.rb

Spree::Product.class_eval do
  include PgSearch::Model

  pg_search_scope :keywords, 
    against: [:name, :description, :meta_description, :meta_keywords],
    using: { tsearch: { dictionary: "english" } }
end
# lib/spree/core/search/pg_search.rb

module Spree
  module Core
    module Search
      class PgSearch < Spree::Core::Search::Base
        def retrieve_products
          Spree::Product.pg_search_by_keywords(@properties[:keywords])
        end
      end
    end
  end
end
# config/initializers/spree.rb

Spree::Config.searcher_class = Spree::Core::Search::PgSearch

Question:

I've updated Rails, Spree (e-commerce framework) and added some new gems. These all needed to be migrated after update and installation. On the development app, I ran into a problem. Whenever I tried to migrate I would get the error

Problem

ActiveRecord::StatementInvalid: PG::DuplicateTable: ERROR: relation "index_products_promotion_rules_on_product_id" already exists : CREATE INDEX "index_products_promotion_rules_on_product_id" ON "spree_products_promotion_rules" ("product_id")

I solved this by dropping my database (DB) and making a new one. Since it's development mode It's not a problem losing some data kept on the DB.

Now I'm using postgreSQL as my DB and Capistrano to deploy my app.

When I try to deploy my website to the live server the error/problem returns, but on the live server I've actually got customer data stored and orders coming in. So I can't drop the DB.

I've tried to remove the line from the migration file and schema.rb, but it hasn't helped.

Questions:

Now I was wondering can I override the current tables on the live server?

Is it save to remove the lines from the migration file and schema.rb ?

Or is there another way to solve this?

schema.rb

  create_table "spree_product_promotion_rules", force: :cascade do |t|
    t.integer "product_id"
    t.integer "promotion_rule_id"
    t.index ["product_id"], name: "index_products_promotion_rules_on_product_id"
    t.index ["promotion_rule_id", "product_id"], name: "index_products_promotion_rules_on_promotion_rule_and_product"

Migration File

# This migration comes from spree (originally 20120831092359)
class SpreePromoOneTwo < ActiveRecord::Migration[4.2]
  def up

    # This migration is just a compressed migration for all previous versions of spree_promo
    return if table_exists?(:spree_products_promotion_rules)

    create_table :spree_products_promotion_rules, :id => false, :force => true do |t|
      t.references :product
      t.references :promotion_rule
    end

    add_index :spree_products_promotion_rules, [:product_id], :name => 'index_products_promotion_rules_on_product_id'
    add_index :spree_products_promotion_rules, [:promotion_rule_id], :name => 'index_products_promotion_rules_on_promotion_rule_id'

    create_table :spree_promotion_actions, force: true do |t|
      t.references :activator
      t.integer    :position
      t.string     :type
    end

    create_table :spree_promotion_rules, force: true do |t|
      t.references :activator
      t.references :user
      t.references :product_group
      t.string     :type
      t.timestamps null: false
    end

    add_index :spree_promotion_rules, [:product_group_id], name: 'index_promotion_rules_on_product_group_id'
    add_index :spree_promotion_rules, [:user_id], name: 'index_promotion_rules_on_user_id'

    create_table :spree_promotion_rules_users, id: false, force: true do |t|
      t.references :user
      t.references :promotion_rule
    end

    add_index :spree_promotion_rules_users, [:promotion_rule_id], name: 'index_promotion_rules_users_on_promotion_rule_id'
    add_index :spree_promotion_rules_users, [:user_id], name: 'index_promotion_rules_users_on_user_id'
  end
end

Answer:

Ultimately I downloaded the database, so I could try to migrate locally. I changed the files removing the already existing lines and executing the rails db:migrate until I no longer got the error message.

Question:

I'm trying to show product related accessories ranked by best selling in my spree store using this:

Spree::Product.class_eval do
  def best_selling_accessories
    accessories.active.select("spree_products.*, SUM(spree_line_items.quantity) AS total_qty, spree_line_items.variant_id").
    joins(:line_items).joins("INNER JOIN spree_orders ON spree_orders.id = spree_line_items.order_id").
    where("spree_orders.state = 'complete'").
    group("spree_line_items.variant_id, spree_products.id").
    order("total_qty DESC")
  end
end

In my controller I'm doing:

@products = @spree_product.best_selling_accessories

When I try to render the view though I get:

PG::UndefinedColumn: ERROR: column "total_qty" does not exist LINE 1: ...line_items.variant_id, spree_products.id ORDER BY total_qty

If I use the better errors shell and manual run the partial render:

render "spree/shared/products", products: @products

It returns the correct html.

Map also returns the correct results from the better errors shell:

>> @products.map { |p| p.total_qty }
=> [14, 10, 7, 7, 4, 3, 3, 3, 3, 3, 1, 1, 1]

So I'm wondering, if it renders correctly in the better errors shell and returns all the correct products when I ask for @products, why does activerecord throw that error?


Answer:

I just needed to call to_a on the result.

I'm still confused as to why I can manually run all the commands and they return the correct result though.