我需要帮助重写一个额外的DB查询，最初是用一个名为Squeel的gem编写的。这个使用稍微不同的语法的别人,我想我可以适应我,但似乎并不成功。
我一直在重写一个叫做排放网关的应用程序，从Rails v3.2到Rails v6.1.4.2。DB查询是我一直卡住的地方。
下面是Squeel查询:
Project.joins{vendor}.joins{certifications.outer}.where{
(projects.vendor_id.eq my{ vendor_id }) |
(vendors.parent_vendor_id.eq my{ vendor_id }) |
((certifications.donaldson == true) & (certifications.published == true))
}.uniq
这是在控制台外的Rails v3.2 SQL。
SELECT DISTINCT "vendors".* FROM "vendors"
INNER JOIN "projects" ON "projects"."vendor_id" = "vendors"."id"
INNER JOIN "certifications" ON "certifications"."project_id" = "projects"."id"
WHERE (("certifications"."donaldson" = 't' AND "certifications"."published" = 't'))
ORDER BY "vendors"."parent_vendor_id", "vendors"."name"
我试着:
Project.joins(vendor: :certifications).where(certifications: { donaldson: 't', published: 't' })
.order(:parent_vendor_id, :name)
和我试着:
Project.joins(:vendor, :certifications).where(certifications: { donaldson: 't', published: 't' })
.order(:parent_vendor_id, :name)
项目。Rb模型(只有关联部分和模式信息)
# == Schema Information
#
# Table name: projects
#
# id :integer not null, primary key
# vendor_id :integer
# fleet_id :integer
# status :string(255) default("incomplete")
# created_at :datetime not null
# updated_at :datetime not null
# user_id :integer
# notes :text
# user_verified_id :integer
# user_submitted_id :integer
# certified :boolean
# cached_user_name :string(255)
# deleted :boolean default(FALSE)
# cached_fleet_name :string(255)
#
class Project < ActiveRecord::Base
attr_accessible :status, :fleet_id, :vendor_id, :notes, :user_verified_id, :user_created_id, :user_submitted_id, :certified, :user_id, :engine_completed, :vehicle_completed, :inspection_completed, :maintenance_completed, :deleted, :cached_fleet_name, :cached_user_name, :cached_vehicle_number
has_one :assessment, dependent: :destroy
has_one :controlled_sale, dependent: :destroy
has_one :datalogger, dependent: :destroy
has_one :estimate, dependent: :destroy
has_many :audits, dependent: :destroy
has_many :certifications, dependent: :destroy
has_many :filters, through: :certifications
has_many :kits, through: :certifications
has_many :pictures, dependent: :destroy
has_many :warranties, dependent: :destroy
has_many :work_orders, dependent: :destroy
belongs_to :fleet
belongs_to :user, touch: true
belongs_to :vendor
belongs_to :verified_by, :class_name => "User", :foreign_key => "user_verified_id"
belongs_to :submitted_by, :class_name => "User", :foreign_key => "user_submitted_id"
delegate :status, :complete?, to: :datalogger, prefix: true, allow_nil: true
delegate :name, to: :vendor, prefix: true
delegate :name, to: :user, prefix: true, allow_nil: true
delegate :name, to: :verified_by, prefix: true, allow_nil: true
delegate :name, to: :submitted_by, prefix: true, allow_nil: true
before_create :cache_associated_data
before_update :cache_user_name, :if => :user_id_changed?
before_update :cache_fleet_name, :if => :fleet_id_changed?
after_create :audit_create
after_update :audit, :if => :status_changed?
after_update :audit_reassignment, :if => :user_id_changed?
after_update :audit_destroy, :if => :vendor_id_changed?
after_update :devalidation_notice, :if => :certified_changed?
after_update :locking_detection, :if => :status_changed?
after_update :prepared_notice, :if => :status_changed?
validates_presence_of :vendor_id
validates_presence_of :user_id, :unless => :deleted?
validates_presence_of :fleet_id, :unless => :deleted?
供应商。Schema和associationjs:
# == Schema Information
#
# Table name: vendors
#
# id :integer not null, primary key
# name :string(255)
# address :string(255)
# contact :string(255)
# email :string(255)
# phone :string(255)
# note :string(255)
# user_manager_id :integer
# created_at :datetime not null
# updated_at :datetime not null
# logo_file_name :string(255)
# logo_content_type :string(255)
# logo_file_size :integer
# logo_updated_at :datetime
# parent_vendor_id :integer
# street :string(255)
# street2 :string(255)
# city :string(255)
# state :string(255)
# zip :integer
# country :string(255)
# region :string(255)
#
class Vendor < ActiveRecord::Base
attr_accessible :name, :phone, :contact, :email, :street, :street2, :city, :state, :zip, :country, :logo, :note, :addresses_attributes, :parent_vendor_id, :user_manager_id, :region_id, :users_attributes
has_many :articles
has_many :projects, dependent: :destroy
has_many :fleets, dependent: :destroy
has_many :users, dependent: :destroy
has_many :subvendors, foreign_key: "parent_vendor_id", class_name: 'Vendor'
has_many :verifications, dependent: :destroy
has_one :subscription, dependent: :destroy
alias_method :original_subscription, :subscription
# certain regions are restricted to select subvendors
has_many :regions
# this vendors region
belongs_to :region
belongs_to :manager, foreign_key: "user_manager_id", class_name: 'User'
belongs_to :parent_vendor, foreign_key: "parent_vendor_id", class_name: 'Vendor'
belongs_to :fleet, touch: true
# CHANGED (allow_blank:true removed) v0.005 9/2/2021 Scott Milella
# delegate :name, to: :manager, prefix: true, allow_blank: true, allow_nil: true
# delegate :name, to: :region, prefix: true, allow_blank: true, allow_nil: true
delegate :name, to: :manager, prefix: true, allow_nil: true
delegate :name, to: :region, prefix: true, allow_nil: true
before_validation :sanitize
validates_presence_of :phone, :email, :street, :city, :state, :zip, :country
validates_presence_of :contact, message: "name for owner cannot be blank"
validates_presence_of :name, message: "of company can't be blank"
validates_presence_of :region_id, :if => :region_required?
validates :name, exclusion: { in: ['donaldson', 'diesel emissions service', 'trash'] }
validates_format_of :phone,
:message => "must be a valid telephone number.",
:with => /[0-9]{10}|[(]{1}[0-9]{0,3}[) -]{0,3}?[0-9]{3}[ -]{0,4}?[0-9]{4}/
accepts_nested_attributes_for :users, reject_if: :all_blank
认证。包含模式和关联信息的Rb模型:
# == Schema Information
#
# Table name: certifications
#
# id :integer not null, primary key
# project_id :integer
# filter_id :integer
# kit_id :integer
# created_at :datetime not null
# updated_at :datetime not null
# published :boolean default(FALSE)
#
class Certification < ActiveRecord::Base
belongs_to :project, touch: true
belongs_to :filter
belongs_to :kit
attr_accessible :project_id, :filter_id, :donaldson, :cdti, :published, :published_at
validates_uniqueness_of :filter_id, scope: :project_id
我添加了模型，因为我得到一个错误，任何时候我尝试:项目。join (vendor:: certificates)我得到一个错误:
Can't join 'Certification' to association named 'vendor'; perhaps you misspelled it?
当我查看认证模型时，它似乎与供应商没有关系，它属于项目。
当我看到供应商模型时，它有许多项目
当我看Project时，它似乎是与两者都有关联的项目，它有许多:认证，它属于:供应商
我已经尝试了很多方法告诉Rails项目。连接(供应商::认证)，但它只是不断给我一个错误的项目试图连接这两个表，但存在一种关系，显然它以某种方式使用Squeel宝石?语法问题?
任何帮助都将非常感激，我相信这是这个APP的最后一个问题，我已经完成了。提前谢谢你，斯科特
Vendor
.joins(:projects, :certifications)
.where(
certifications: {
# use a boolean and let the DB adapter cast it
donaldson: true,
published: true
}
)
.order(:parent_vendor_id, :name)
.destinct