ARRGH! Unconventional tables and RoR

I’m somewhat new to RoR and can’t seem to find a solution to an issue
I’m having. Assuming I have two tables: frogs and frog_pairs that are
setup like so

frogs

  • id int(11)
  • name varchar(50)

frog_pairs

  • id int(11)
  • species_friend int(11) (foreign key)
  • species_foe int(11) (foreign key)

I know that Rails would rather see ‘frog_id’ as a foreign key inside the
frog_pairs table in order to make an associating to the frogs table,
but I need to actually create a “pair” based on two frogs.

I have been able to successfully add a frog pair to frog_pairs by
inserting two differnt id’s from frogs, but how would I go about
putting the two frog (names) out to my view…

I want my (list) view to look like this:

Frog Pair Listings
id species_friend species_foe

  1. 1 African Dwarf Poison Dart
  2. 2 Argentine Horned Fire-bellied

but it looks like this right now:

Frog Pair Listings
id species_friend species_foe

  1. 1 2 5
  2. 2 3 1

…and I’d like to have my (show) view look like this:

Showing Frog Pair 2

Species Friend

  • Argentine Horned

Species Foe

  • Fire-bellied

…instead of the obvious:

Showing Frog Pair 2

Species Friend

  • 3

Species Foe

  • 1

If this is something I have to override RoR’s foreign key behavior or
use something like find_by_sql, could anyone provide an explicit example
from model to controller to view? Thanks!

(I hope I have not confused everybody more than I am already!)

I’m not a self-referential association guru, but since you’re not
getting an immediate response, I’ll take a shot. Assuming that your
species_foe and species_friend actually refer to id’s in your frogs
table, then I think you can self-refer the frogs table to itself a
couple of ways, such as (in your frog.rb model):

belongs_to :foe, :class_name => “Frog”, :foreign_key => “species_foe”
has_many :foe_frogs, :class_name => “Frog”, :foreign_key =>
“species_foe”

belongs_to :friend, :class_name => “Frog”, :foreign_key =>
“species_friend”
has_many :friend_frogs, :class_name => “Frog”, :foreign_key =>
“species_friend”

Then I think you’ll be able to do something along the lines of …

@frog = Frog.find(1)
@frog.name
@frog.foe.name
@frog.friend.name

I’m not sure on the exact details - sorry I can’t give you more specific
details. AWDWR has some discussion of self-referential associations.

c.

Rick A. wrote:

I’m somewhat new to RoR and can’t seem to find a solution to an issue
I’m having. Assuming I have two tables: frogs and frog_pairs that are
setup like so

frogs

  • id int(11)
  • name varchar(50)

frog_pairs

  • id int(11)
  • species_friend int(11) (foreign key)
  • species_foe int(11) (foreign key)

I know that Rails would rather see ‘frog_id’ as a foreign key inside the
frog_pairs table in order to make an associating to the frogs table,
but I need to actually create a “pair” based on two frogs.

I have been able to successfully add a frog pair to frog_pairs by
inserting two differnt id’s from frogs, but how would I go about
putting the two frog (names) out to my view…

I want my (list) view to look like this:

Frog Pair Listings
id species_friend species_foe

  1. 1 African Dwarf Poison Dart
  2. 2 Argentine Horned Fire-bellied

but it looks like this right now:

Frog Pair Listings
id species_friend species_foe

  1. 1 2 5
  2. 2 3 1

…and I’d like to have my (show) view look like this:

Showing Frog Pair 2

Species Friend

  • Argentine Horned

Species Foe

  • Fire-bellied

…instead of the obvious:

Showing Frog Pair 2

Species Friend

  • 3

Species Foe

  • 1

If this is something I have to override RoR’s foreign key behavior or
use something like find_by_sql, could anyone provide an explicit example
from model to controller to view? Thanks!

(I hope I have not confused everybody more than I am already!)

Cayce B. wrote:

I’m not a self-referential association guru, but since you’re not
getting an immediate response, I’ll take a shot. Assuming that your
species_foe and species_friend actually refer to id’s in your frogs
table, then I think you can self-refer the frogs table to itself a
couple of ways, such as (in your frog.rb model):

belongs_to :foe, :class_name => “Frog”, :foreign_key => “species_foe”
has_many :foe_frogs, :class_name => “Frog”, :foreign_key =>
“species_foe”

belongs_to :friend, :class_name => “Frog”, :foreign_key =>
“species_friend”
has_many :friend_frogs, :class_name => “Frog”, :foreign_key =>
“species_friend”

Then I think you’ll be able to do something along the lines of …

@frog = Frog.find(1)
@frog.name
@frog.foe.name
@frog.friend.name

I’m not sure on the exact details - sorry I can’t give you more specific
details. AWDWR has some discussion of self-referential associations.

c.

Unless I am misunderstanding, this didn’t seem to do the trick.

However I did find this:
http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through
which is VERY similar. The only problem I’m having now is how to output
it to the view (controller/view scenario). Thanks for any help!

On 10/4/06, Rick A. [email protected] wrote:

has_many :foe_frogs, :class_name => “Frog”, :foreign_key =>
@frog.name
@frog.foe.name
@frog.friend.name

I’m not sure on the exact details - sorry I can’t give you more specific
details. AWDWR has some discussion of self-referential associations.

c.

I think this should be:

class FrogPair < ActiveRecord::Base
belongs_to :friend, :class_name=>‘Frog’,
:foreign_key=>‘species_friend’
belongs_to :foe, :class_name=>‘Frog’, :foreign_key=>‘species_foe’
end

That allows you to do

fp = FrogPair.find(2)
and then
fp.friend.name
fp.foe.name

If you want access to a list of pairs where a specific frog is the
friend or
foe respectively, do:

class Frog < ActiveRecord::Base
has_many :pairs_as_friend, :class_name=>‘FrogPair’,
:foreign_key=>‘species_friend’
has_many :pairs_as_friend, :class_name=>‘FrogPair’,
:foreign_key=>‘species_foe’
end

Thinking of it, I think you have the semantics of your data model wrong.
If
you want to express relationships between different frog species, you
are
better off separating it into two different tables:

Frog
FrogFriends
FrogFoes

where FrogFriends and FrogFoes may look something like this:

frog_id friend_id
and
frog_id foe_id

The common problem with these reciprocal relationships is that you will
probably have to insert two records into each of the tables:

some_frog_species_instance.add_friend( another_frog_species_instance )
should result in two “friend” entries, with the values reversed, so you
can
find all friends of a certain species by the condition “FROM
frog_friends
WHERE frog_id = some_frog_species_id”, rather than “FROM frog_friends
WHERE
frog_id=some_frog_species_id OR friend_id=some_frog_species_id”

Cheers,
Max