Specify a foreign key in Sequel model

Question

I have two models:

Foo:

class Foo < Sequel::Model (:FOO_TABLE)

  set_primary_key [:KEY]

  # has many Bars
  one_to_many :bars

end

Bar:

class Bar < Sequel::Model (:BAR_TABLE)

  # compound key
  set_primary_key [:KEY,:NBR]

  # belongs to Foo
  many_to_one :foo

end

Loading Foo works as expected:

irb> foo = Foo['ABC']
=> #<Foo @values={:KEY=>"ABC", :NAME=>"ABC name"}>

However, when I attempt to load its Bars, I get an error:

irb> bars = foo.bars
=> Sequel::DatabaseError: Mysql2::Error: Unknown column 'BAR_TABLE.foo_id' in 'where clause'

What is the right way to specify a foreign_key in my Sequel model?

** edit **

Using MySQL2.

You need to pose the schema of your tables for proper help. – Phrogz Jan 22 '15 at 15:59 — Phrogz, Jan 22 '15 at 15:59

Phrogz · Accepted Answer · 2015-01-22T16:14:05.863

In general you want to look at the options for associations. Specifically, you need to tell Sequel how to find the correct 'bars' given a foo.

If you are just using a non-standard name for the foreign key reference in your BAR_TABLE, you can do this:

class Foo < Sequel::Model(:BAR_TABLE)
  one_to_many :bars, :key => :my_foo_id
end

class Bar < Sequel::Model(:BAR_TABLE)
  many_to_one :foo, :key => :my_foo_id
end

Example/proof:

require 'sequel'
DB = Sequel.sqlite
DB.create_table(:FOOBOY){ Integer :myd; String :name; primary_key [:myd] }
DB.create_table(:BARSTON){ Integer :myd; String :name; foreign_key :my_foo_id, :FOOBOY; primary_key [:myd] }

DB[:FOOBOY] << { myd:1, name:'Furst' }
DB[:FOOBOY] << { myd:2, name:'Surkind' }

DB[:BARSTON] << { myd:1, name:'Bobby', my_foo_id:1 }
DB[:BARSTON] << { myd:2, name:'Jimmy', my_foo_id:1 }
DB[:BARSTON] << { myd:3, name:'XYZZY', my_foo_id:2 }

class Foo < Sequel::Model(:FOOBOY)
  one_to_many :bars, :key => :my_foo_id
end

class Bar < Sequel::Model(:BARSTON)
  many_to_one :foo, :key => :my_foo_id
end

Foo[1]
#=> #<Foo @values={:myd=>1, :name=>"Furst"}>

Foo[1].bars
#=> [#<Bar @values={:myd=>1, :name=>"Bobby", :my_foo_id=>1}>, #<Bar @values={:myd=>2, :name=>"Jimmy", :my_foo_id=>1}>]

Foo[1].bars.last.foo
#=> #<Foo @values={:myd=>1, :name=>"Furst"}>

Bar.last
#=> #<Bar @values={:myd=>3, :name=>"XYZZY", :my_foo_id=>2}>

Bar.last.foo
#=> #<Foo @values={:myd=>2, :name=>"Surkind"}>

My pleasure. While I do appreciate you paring your question down to generic names, in the future please be sure to show how those map to the actual schema of the tables involved. — Phrogz, Jan 22 '15 at 16:17
Any recommendations on the best way to display the schema? I doubt SO supports graphviz. — craig, Jan 22 '15 at 16:18
If you were using Postgres I'd suggest a `\dt` on the tables involved. I don't know the equivalent in MySQL2. Alternatively, you can show the schema as Sequel understands it using `DB.schema(:TABLENAME)` for each table. — Phrogz, Jan 22 '15 at 16:26

Specify a foreign key in Sequel model

1 Answers1