# Composite primary keys and :joins=\>

**URL:** <https://www.ruby-forum.com/t/composite-primary-keys-and-joins/193529>\
**Category:** Rails\
**Created:** [August 19, 2010, 7:52am UTC](https://www.ruby-forum.com/t/composite-primary-keys-and-joins/193529 "2010-08-19T07:52:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![JeffV](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/5fc32e/32.png) [@JeffV](https://www.ruby-forum.com/u/JeffV)\
**Post date:** [August 19, 2010, 7:52am UTC](https://www.ruby-forum.com/t/composite-primary-keys-and-joins/193529/1 "2010-08-19T07:52:09Z")

</div>

I have a legacy db with the following simplified structure:

Table-A:  
type\_key, code\_key, name, … # PKs are type\_key and code\_key,  
there is no id col and I cannot alter this db

Table-B: # each row has only the code as a foreign\_key, the type\_key  
is hard-coded to “FOOKEY”  
an\_id, code, …

Models:  
class TableB \< AR::Base  
set\_table\_name ‘table\_b’  
set\_primary\_key :an\_id # singular PK

belongs\_to :code\_name, :class\_name=\>‘TableA’,  
:primary\_key=\>:pk\_code\_name, :foreign\_key=\>:code

```
 SELECT = "*, table_a.name AS pk_name"

 def self.get_all
    all(:select=>SELECT, :joins=>:code_name)
 end

```

end

class TableA \< AR::Base  
set\_table\_name ‘table\_a’  
set primary\_keys :type\_key, :code\_key # using the composite  
primary keys gem 2.3.2

```
 def self.find_by_pk_code_name(*args) # pk method invoked by

```

belongs\_to in TableB  
opts = args.extract\_options!  
pk\_type\_key = “FOOKEY”  
pk\_code\_key = args.first  
find([pk\_type\_key, pk\_code\_key], opts)  
rescue ActiveRecord::RecordNotFound  
end  
end

Console:  
x=TableB.first  
x.code\_name # works!  
TableB.get\_all # fails: “element size differs (1 should be 2)”

I believe this error is due to 2 PKs being defined for TableA yet only  
1 key is specified in the belongs\_to :foreign\_key.

The problem is that a row in TableB contains only one of the PKs for  
TableA, with the second PK being hard-coded to “FOOKEY”.  
It appears that the contents of :foreign\_key=\> references col names,  
not methods. The SQL I want to generate is something like this:  
SELECT \*, table\_a.name AS pk\_name FROM table\_b  
LEFT OUTER JOIN table\_a ON (table\_a.code\_key = table\_b.code AND  
table\_a.type\_key = ‘FOOKEY’)

I cannot figure out how to use AR to make this work. Any help is much  
appreciated! Thanks!

---

<div class="post-metadata">

**Author:** ![JeffV](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/5fc32e/32.png) [@JeffV](https://www.ruby-forum.com/u/JeffV)\
**Post date:** [August 19, 2010, 9:59pm UTC](https://www.ruby-forum.com/t/composite-primary-keys-and-joins/193529/2 "2010-08-19T21:59:57Z")

</div>

as a follow up…I did the equivalent of:  
TableB.all(:select=\>SELECT, :joins=\>“left outer join table\_a on  
table\_a.type\_key=‘FOOKEY’ and table\_a.code\_key=table\_b.code”)

and it works fine. Still curious to know if there is a way to define  
a belongs\_to and use that association in the :joins.

Thanks,  
Jeff