search

vinsol-spree-contrib / spree_marketing

Spree extension that allow a lot of reports synced with Mailchimp to help you in creating different subscribers list on Mailchimp. This extension provides reports like "Most Selling Products", "Abandoned Carts", "Most searched keywords", etc. to give better insights.
BSD 3-Clause "New" or "Revised" License
7 stars 18 forks source link

PG Error while running rake task for smart list #86

Closed gopalshimpi closed 5 years ago

gopalshimpi commented 5 years ago

After running rake task rake 'spree_marketing:smart_list:generate' Got following error.

rake aborted!
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "spree_addresses.state_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  "state_id" FROM "spree_orders" INNER JOIN "spree_add...
                ^
: SELECT  "state_id" FROM "spree_orders" INNER JOIN "spree_addresses" ON "spree_addresses"."id" = "spree_orders"."bill_address_id" INNER JOIN "spree_states" ON "spree_states"."id" = "spree_addresses"."state_id" WHERE (spree_orders.completed_at >= '2018-09-04 12:10:21.975428') GROUP BY spree_states.id ORDER BY COUNT(spree_orders.id) DESC LIMIT $1
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activerecord-5.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:603:in `async_exec'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activerecord-
5.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:603:in `block (2 levels) in exec_no_cache'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activesupport-5.2.0/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activesupport-5.2.0/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activesupport-5.2.0/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activerecord-5.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `block in exec_no_cache'
/Users/gopal/.rvm/gems/ruby-2.5.1@angularspree-api/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
gopalshimpi commented 5 years ago

Fixed this issue. we need to create a decorator for it. app/models/spree/marketing/list

Spree::Marketing::List::MostZoneWiseOrders.class_eval do
  def self.data
    Spree::Order.joins(bill_address: :state)
                .where('spree_orders.completed_at >= :time_frame', time_frame: computed_time)
                .group('spree_states.id, spree_addresses.state_id')
                .order(Arel.sql("COUNT(spree_orders.id) DESC"))
                .limit(5)
                .pluck(:state_id)
  end
end

Use same way if you encounter any group by clause error.