Rails 'includes' and 'where' with belongs_to and has_many association

user_19313362 0
Viewed 73

I have models:

class Order < ApplicationRecord
  acts_as_paranoid
  has_paper_trail

  enum status: %i[created in_process]

  has_many :order_containers
  has_many :line_items
end

class LineItem < ApplicationRecord
  acts_as_paranoid
  has_paper_trail

  enum status: %i[in_process collected]

  belongs_to :order
  belongs_to :variant
end

class Variant < ApplicationRecord
  acts_as_paranoid
  has_paper_trail

  has_many :line_items

  belongs_to :product

  validates :barcode, presence: true
end

class Product < ApplicationRecord
  acts_as_paranoid
  has_paper_trail

  belongs_to :isles, required: false
  has_many :variants

  validates :name, :volume, :sku, :price, presence: true
end

class Isle < ApplicationRecord
  acts_as_paranoid
  has_paper_trail

  has_many :products
  validates :name, presence: true
end

I need to output only those orders in which the products belong to a specific island. For example, if there are no products in the order that belong to the island I need, then this order and its products do not need to be displayed. And if there are products in the order that belong to a specific island for example (isle.id 1), then such an order needs to be withdrawn and those products that belong to this department

I try this:

@products = Order.includes([:line_items, :variants, :products, :isles]).where('products.isle_id = isle.id').references(:orders)

but i got error:

ailure/Error: return { "#{root_name}": [] } if records.blank?
 
 ActiveRecord::StatementInvalid:
   PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "products"
   LINE 1: ..."orders" WHERE "orders"."deleted_at" IS NULL AND (products.i...

I'm sorry if I didn't design well, I'm a novice developer, and here's my first assignment)

rails-activerecord postgresql ruby-on-rails ruby database
2 Answers

This will return all products in Order#1 from Isle#1. If order has multiple variants from the same product it will return duplicate products, if this is not what you need add .distinct to these queries.

>> order = Order.first
>> isle  = Isle.first

>> Product.joins(variants: { line_items: :order }).where(isle_id: isle, line_items: { order_id: order })
=> [#<Product:0x00007f1551fc4820 id: 1, isle_id: 1>,
    #<Product:0x00007f1551fc4258 id: 2, isle_id: 1>]

You can add a few associations to Order to simplify this:

class Order < ApplicationRecord
  has_many :line_items

  has_many :variants, through: :line_items
  has_many :products, through: :variants
end

>> Order.first.products.where(isle_id: Isle.first)
=> [#<Product:0x00007f154babcb30 id: 1, isle_id: 1>, 
    #<Product:0x00007f154babca18 id: 2, isle_id: 1>]

Update

Make sure you're creating the associations correctly. Use create! and save! methods in the console to raise any validation errors.

# NOTE: create a new order with two products in two different isles
#       just add the required attributes from your models.

order = Order.create!(line_items: [
  LineItem.new(variant: Variant.new(product: Product.new(isle: (isle = Isle.create!)))),
  LineItem.new(variant: Variant.new(product: Product.new(isle: Isle.new)))
])

# NOTE: verify that you have two products

>> order.products
=> [#<Product:0x00007f6f1cb964e0 id: 1, isle_id: 1>,
    #<Product:0x00007f6f1cb963f0 id: 2, isle_id: 2>]

# NOTE: filter by isle

>> order.products.where(isle_id: isle)
=> [#<Product:0x00007f6f1ccda630 id: 1, isle_id: 1>]

>> order.products.where(isle_id: 2)
=> [#<Product:0x00007f6f1d140cd8 id: 2, isle_id: 2>]

https://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many

https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

user_2070900

You have quite a few issues with the structure of that. If you truly just want the products for a specific Isle then you should be able to just use:

@products = Product.where(isle_id: my_isle_variable)

You also probably need to update the models so that Product belongs_to :isle (singular not plural)

user_90351020
Related