Activerecord加入不返回所有预期的结果

Question

我有一个预先存在的postgres数据库，我试图查询。我已经建立了两个模型，客户和设备。我试图查询两个表与一个非标准的密钥加入他们。无论我尝试了什么，我只能从一张桌子上得到结果。我试着改变ActiveRecord行为来使用非标准键以及find_by_sql（）。由于现有的表结构，我也禁用了默认表复数。我错过了明显的东西吗？

ActiveRecord::Base.pluralize_table_names = false 

    class Customer < ActiveRecord::Base 
     self.primary_key = :acct 
     has_many :equipment, foreign_key: 'acct' 
    end 

    class Equipment < ActiveRecord::Base 
     self.primary_key = :acct 
     belongs_to :customer, foreign_key: 'acct' 
    end 

    [email protected]:~/rails_workspace/pg_test$ rails c 
    Loading development environment (Rails 4.2.0.rc2) 
    2.2.1 :001 > Customer.first() 
     Customer Load (16.9ms) SELECT "customer".* FROM "customer" ORDER BY "customer"."acct" ASC LIMIT 1 
    => #<Customer acct: "000002", franchise: "501", name: "NAME", address: "STREET ADDR", city: "CITY", state: "ST", zip: "ZIPCODE", phone: "1234567890", email: "[email protected]", installdate: "2014-02-20", consumptiondate: nil, parent: "", sentoverage: false, exempt: false, pin: "", locked: false, status: "ACT"> 
    2.2.1 :002 > Equipment.first() 
     Equipment Load (128.3ms) SELECT "equipment".* FROM "equipment" ORDER BY "equipment"."acct" ASC LIMIT 1 
    => #<Equipment mac: "26225070", acct: "000002"> 
    2.2.1 :003 > 


    2.2.1 :009 > Customer.find_by_sql("Select c.*, e.* from customer as c, equipment as e where e.acct = c.acct and c.acct = '000002' limit 1") 

    Customer Load (53.9ms) Select c.*, e.* from customer as c, equipment as e where e.acct = c.acct and c.acct = '000002' limit 1 
    => [#<Customer acct: "000002", franchise: "501", name: "NAME", address: "STREET ADDR", city: "CITY", state: "ST", zip: "ZIPCODE", phone: "1234567890", email: "[email protected]", installdate: "2014-02-20", consumptiondate: nil, parent: "", sentoverage: false, exempt: false, pin: "", locked: false, status: "ACT">] 

    2.2.1 :010 > Customer.joins('FULL OUTER JOIN equipment ON equipment.acct = customer.acct').limit(1) 
     Customer Load (119.9ms) SELECT "customer".* FROM "customer" FULL OUTER JOIN equipment ON equipment.acct = customer.acct LIMIT 1 
    => #<ActiveRecord::Relation [#<Customer acct: "000002", franchise: "501", name: "NAME", address: "STREET ADDR", city: "CITY", state: "ST", zip: "ZIPCODE", phone: "1234567890", email: "[email protected]", installdate: "2014-02-20", consumptiondate: nil, parent: "", sentoverage: false, exempt: false, pin: "", locked: false, status: "ACT">]> 

    2.2.1 :011 > Customer.joins(:equipment) 
    ActiveRecord::ConfigurationError: Association named 'equipment' was not found on Customer; perhaps you misspelled it? 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:218:in `find_reflection' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:223:in `block in build' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:222:in `each' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:222:in `map' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:222:in `build' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/associations/join_dependency.rb:99:in `initialize' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation/query_methods.rb:1039:in `new' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation/query_methods.rb:1039:in `build_joins' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation/query_methods.rb:863:in `build_arel' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation/query_methods.rb:855:in `arel' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation.rb:638:in `exec_queries' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation.rb:514:in `load' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation.rb:243:in `to_a' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activerecord-4.2.0.rc2/lib/active_record/relation.rb:629:in `inspect' 
    from /home/me/.rvm/gems/[email protected]_test/gems/railties-4.2.0.rc2/lib/rails/commands/console.rb:110:in `start' 
    from /home/me/.rvm/gems/[email protected]_test/gems/railties-4.2.0.rc2/lib/rails/commands/console.rb:9:in `start' 
    from /home/me/.rvm/gems/[email protected]_test/gems/railties-4.2.0.rc2/lib/rails/commands/commands_tasks.rb:68:in `console' 
    from /home/me/.rvm/gems/[email protected]_test/gems/railties-4.2.0.rc2/lib/rails/commands/commands_tasks.rb:39:in `run_command!' 
    from /home/me/.rvm/gems/[email protected]_test/gems/railties-4.2.0.rc2/lib/rails/commands.rb:17:in `<top (required)>' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:274:in `require' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:274:in `block in require' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:240:in `load_dependency' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:274:in `require' 
    from /home/me/rails_workspace/pg_test/bin/rails:8:in `<top (required)>' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:268:in `load' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:268:in `block in load' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:240:in `load_dependency' 
    from /home/me/.rvm/gems/[email protected]_test/gems/activesupport-4.2.0.rc2/lib/active_support/dependencies.rb:268:in `load' 
    from /home/me/.rvm/rubies/ruby-2.2.1/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require' 
    from /home/me/.rvm/rubies/ruby-2.2.1/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require' 
    from -e:1:in `<main>'2.2.1 :012 > 

编辑

改性的设备类改变belongs_to的：设备到：设备。导致修复ActiveRecord :: ConfigurationError：关联名为'设备'错误。但仍然没有解决的加入。如果我使用Customer表中的“key”手动查询Equipment表，则会找到数据记录。

2.2.1 :008 > Customer.joins(:equipment).limit(1) 
    Customer Load (11.9ms) SELECT "customer".* FROM "customer" INNER JOIN "equipment" ON "equipment"."acct" = "customer"."acct" LIMIT 1 
=> #<ActiveRecord::Relation > [#<Customer acct: "000002", franchise: "501", name: "NAME", address: "STREET ADDR", city: "CITY", state: "ST", zip: "ZIPCODE", phone: "1234567890", email: "[email protected]", installdate: "2014-02-20", consumptiondate: nil, parent: "", sentoverage: false, exempt: false, pin: "", locked: false, status: "ACT">] 

2.2.1 :017 > Equipment.where(acct: '000002') 
    Equipment Load (76.5ms) SELECT "equipment".* FROM "equipment" WHERE "equipment"."acct" = $1 [["acct", "000002"]] 
=> #<ActiveRecord::Relation [#<Equipment mac: "26225070", acct: "000002">, #<Equipment mac: "23126536", acct: "000002">, #<Equipment mac: "00E06F76A08A", acct: "000002">]> 

Answer 1

对于一个简单的解决方案scopeby_customer在Equipment模型定义，

class Equipment < ActiveRecord::Base 
    ... 
    scope :by_customer,->(customer_conditions) { joins(:customer).where(customer: customer_conditions)}  
end 

，并按照Customer模型scopeby_equipment，

class Customer < ActiveRecord::Base 
    ... 
    scope :by_equipment,->(equipment_conditions) { joins(:equipment).where(equipment: equipment_conditions)} 
end 

To find Equipments of Customer '000002'

@equipments = Equipment.by_customer(acct: '000002') 

To find Customer who has an equipment of mac address is '26225070'

@customer = Customer.by_equipment(mac: '26225070') 

Answer 2

我想我可能会看到你错过了什么。

Customer.joins(:anything)将返回Customer的集合。

Equipment.joins(:anything)将返回Equipment的集合。

也许你已经明白，但也许不是。

如果你想要一个结果集是两者的结合，我想你必须完全从另一个角度来看它，而且我认为我需要更好地理解你最终的结果之后才能提供任何好的建议。

编辑：拿装备属于特定客户：

Equipment.joins(:customer).where('customer.acct = ?', '000002') 

要获得客户对设备mac：

Customer.joins(:equipment).where('equipment.mac = ?', '26225070')