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 write a query that should output only those products in order that belong to the island
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)
CodePudding user response:
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)
CodePudding user response:
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>]
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