# ARRGH! Unconventional tables and RoR

**URL:** <https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955>\
**Category:** Rails\
**Created:** [October 4, 2006, 8:04am UTC](https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955 "2006-10-04T08:04:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/34f0e0/32.png) [@kly](https://www.ruby-forum.com/u/kly)\
**Post date:** [October 4, 2006, 8:04am UTC](https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955/1 "2006-10-04T08:04:32Z")

</div>

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!)

---

<div class="post-metadata">

**Author:** ![kly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/34f0e0/32.png) [@kly](https://www.ruby-forum.com/u/kly)\
**Post date:** [October 4, 2006, 9:01am UTC](https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955/2 "2006-10-04T09:01:58Z")

</div>

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!)

---

<div class="post-metadata">

**Author:** ![kly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/34f0e0/32.png) [@kly](https://www.ruby-forum.com/u/kly)\
**Post date:** [October 4, 2006, 3:02pm UTC](https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955/3 "2006-10-04T15:02:22Z")

</div>

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](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!

---

<div class="post-metadata">

**Author:** ![kly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/34f0e0/32.png) [@kly](https://www.ruby-forum.com/u/kly)\
**Post date:** [October 6, 2006, 2:58am UTC](https://www.ruby-forum.com/t/arrgh-unconventional-tables-and-ror/70955/4 "2006-10-06T02:58:11Z")

</div>

On 10/4/06, Rick A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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