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
On Aug 18, 10:34 pm, JeffV
<jeff.h.va...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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!
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.