CODEWITHSUNDEEP
ruby-on-railsactiverecord
code-gijoe
code-gijoe

Reputation: 7234

How to full join two rail models?

I'm trying to join the results of 'SupplierShippingItem' and 'MtlSystemItem' but I keep getting an error:

Association named 'mtl_system_items' was not found; perhaps you misspelled it?

My association is done like this:

SupplierShippingItem.joins(:mtl_system_items).where('supplier_shipping_items.inventory_item_id = mtl_system_items.inventory_item_id SEGMENT1 ILIKE ? OR DESCRIPTION ILIKE ? ', "%#{params[:term]}%", "%#{params[:term]}%")

SupplierShippingItem

class SupplierShippingItem < ActiveRecord::Base

  attr_accessible :inventory_item_id, :received_qty, :shipped_qty, :supplier_shipping_list_id, :supplier_planning_schedule_id, :po_number

  belongs_to :mtl_system_item, :foreign_key => :inventory_item_id


end

*MtlSystemItem *

class MtlSystemItem < ActiveRecord::Base

  attr_accessible :inventory_item_id, :segment1, :description, :primary_uom_code, :inventory_item_status_code, :item_type

  has_many :supplier_shipping_items, :foreign_key => :inventory_item_id
end

What I'm trying to achieve is to fetch the items in MtlSystemItem but only if they are found in SupplierShippingItem. I have thousands of items in MtlSystemItem so I want to filter them out a bit. I'll also include a date restriction later on, but I'm blocked by the error.

Upvotes: 0

Views: 947

Answers (2)

AllenC
AllenC

Reputation: 2754

SupplierShippingItem.joins(:mtl_system_items)


 should be


SupplierShippingItem.joins(:mtl_system_item)

on your example above: 

class SupplierShippingItem < ActiveRecord::Base
  belongs_to :mtl_system_item
end

class MtlSystemItem < ActiveRecord::Base
   has_many :supplier_shipping_items
end

you can try this ActiveRecord joins:

SupplierShippingItem.find(:all, :joins => :mtl_system_item])

and you can add conditions for this query like this:

 SupplierShippingItem.find(:all, :joins => :mtl_system_item, :conditions => ["supplier_shipping_items.id = ?", 1]])

Upvotes: 1

jvnill
jvnill

Reputation: 29599

as the error says, the association is not found. You used mtl_system_items instead of mtl_system_item (singular) which is the association you declared.

Remember that for joins and includes, you need to use the association name. For where, use the table name

SupplierShippingItem.joins(:mtl_system_item)
  .where('supplier_shipping_items.inventory_item_id = mtl_system_items.inventory_item_id SEGMENT1 ILIKE ? OR DESCRIPTION ILIKE ? ', "%#{params[:term]}%", "%#{params[:term]}%")

Upvotes: 2

Related Questions