CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3activerecordrails-activerecord
SEJU
SEJU

Reputation: 1125

Rails: active-records query for entry in range & included in

I am working on a shipping implementation for a checkout process.

My app has carts, cart_items, orders and order_items.

Weight and size of all items are in the database and I calculate total_weight in the order and cart models. I also have a shipping_service model with weightmin and weightmax for each shipping service + a postzone and land (country) model.

Now I would like to show on the shopping cart page only the shipping services which are conform to the weight of the cart or order.

I suppose my carts_controller should be something like:

class CartsController < ApplicationController
def show
    @cart = Cart.find(params[:id])
    @lands = Land.find(:all)
    @shippingservices = Shippingservice.where('@cart.total_weight BETWEEN ? AND ?', :weightmin, :weightmax)
end

My cart model is:

class Cart < ActiveRecord::Base
  attr_accessor :total_weight

  has_many :cart_items
  has_many :products, :through => :cart_items
  has_many :lands
  has_many :shipping_services, :through => :postzones

  def total_weight
    cart_items.inject(0) {|sum, n| n.weight * n.amount + sum}
  end
end

My land model is

class Land < ActiveRecord::Base
  has_many :shippingservices, :through => :postzones
  has_many :postzones
  has_many :carts
end

My shipping_service model is:

class Shippingservice < ActiveRecord::Base
  has_many :lands, :through => :postzones
  has_many :postzones
  has_many :carts
  has_many :orders
end

My postzone model is:

class Postzone < ActiveRecord::Base
  belongs_to :shippingservice
  belongs_to :land
end

The postzone table has foreign keys for lands and shipping_services.

Latter I would like to implement two selector fields: one for ship_to_countries and one for shipping_services, with the second selector being populate only with entries related to the entry selected in the first selector.

I had already this working inside the carts_controller:

@shippingservices = Shippingservice.includes(:lands, :postzones).where('postzones.land_id = ?', Land.first.id)

Which load only shipping services for a specific country into the second selector. But I do not know how to combine the two where clauses relative to weight and postzone into one query.

Any help is very much appreciated!

Thank you in advance.

Upvotes: 0

Views: 230

Answers (1)

kevcha
kevcha

Reputation: 1012

The method total_weight is a ruby method which is defined in the model Cart

Then you cannot call this method within an SQL statement.

You need to calculate the total weight in the SQL statement.

You should try something like 

@shippingservices = Shippingservice.joins(carts: :cart_items).where(
  '(cart_items.weight * cart_items.amount) BETWEEN ? AND ?', :weightmin, :weightmax
)

I didn't try but I think it should work :)

Upvotes: 2

Related Questions