CODEWITHSUNDEEP
mysqlruby-on-rails-3activerecord
sufu90
sufu90

Reputation: 144

Rails 3 using sum() in where clause

I have a little problem with my where clause.

PurchasePosition.where(:purchase_order_id => 996).where('sum(pallet_purchase_position_assignments.quantity) < purchase_positions.quantity').includes(:pallet_purchase_position_assignments)

My Models:

class PurchasePosition < ActiveRecord::Base
  has_many :pallet_purchase_position_assignments, :class_name => "PalletPurchasePositionAssignment"
  has_many :pallets, :class_name => "Pallet", :through => :pallet_purchase_position_assignments
end

class Pallet < ActiveRecord::Base
  has_many :pallet_purchase_position_assignments, :class_name => "PalletPurchasePositionAssignment"
  has_many :purchase_positions, :class_name => "PurchasePosition", :through => :pallet_purchase_position_assignments
end

class PalletPurchasePositionAssignment < ActiveRecord::Base
  belongs_to :pallet, :class_name => "Pallet", :foreign_key => "pallet_id"
  belongs_to :purchase_position, :class_name => "PurchasePosition", :foreign_key => "purchase_position_id"
end

All I get is a Mysql error

ActiveRecord::StatementInvalid: Mysql2::Error: Invalid use of group function

I have absolutly no idea where my error lies :-/

Do someone have a solution for my problem?

Michael

Upvotes: 2

Views: 3499

Answers (2)

Shawn Bower
Shawn Bower

Reputation: 1147

The error your getting is because you are not using a group by clause.

In mysql and most databases if you want to use a summation (or any group by column) in a select than you use the HAVING clause not the WHERE clause

PurchasePosition.where(:purchase_order_id => 996).having('sum(pallet_purchase_position_assignments.quantity) < purchase_positions.quantity').includes(:pallet_purchase_position_assignments).sum("pallet_purchase_position_assignments.quantity

For Example if I have a table like:

 create_table "leaders", :force => true do |t|
   t.integer  "leaderable_id"
   t.string   "leaderable_type"
   t.integer  "county_id"
   t.integer  "us_state_id"
   t.integer  "recruits"
   t.integer  "hours"
   t.datetime "created_at"
   t.datetime "updated_at"
   t.datetime "last_run_dt"
 end

Then I can create a having query like this

Leader.having("sum(hours) > 150").group(:leaderable_type).sum(:hours)

Im not sure I have the syntax right for your query but you should be able to fix it up using the having clause and the group clause.

Upvotes: 4

Jan S
Jan S

Reputation: 1837

You cannot use a SUM() function in a where clause .where('sum(pallet_purchase_position_assignments.quantity) < purchase_positions.quantity') - that is throwing the error.

the clause should be like where purchase_positions.quantity > (select sum(purchase_positions.quantity))

Upvotes: 0

Related Questions