Rails 3 joins with non-standard key fields

Question

I have googled myself almost to death over this and the closest I came to anything similar is this stack overflow question (which attempts to ask several questions at once). I have only one. OK, two - but providing an answer to the first will get your answer accepted as long as it adheres to the requirements below.

I am using Rails 3 and Ruby 1.8.7 with a legacy database. The only thing that is up for debate is the Rails version. I am stuck with Ruby 1.8.7 and the database structure.

Here are the significant portions of the models involved:

class List < ActiveRecord::Base
    set_primary_key "ListID"

    has_many :listitem, :foreign_key => "ListID", :dependent => :destroy
    has_many :extra_field, :foreign_key => "ListID", :dependent => :destroy
end

class Listitem < ActiveRecord::Base
    set_table_name "ListItems"
    set_primary_key "ListItemID"

    belongs_to :list
    has_many :list_item_extra_field, :foreign_key => 'ListItemID', :dependent => :destroy
end

This is what I get in rails console:

irb(main):001:0> List.joins(:listitem).to_sql
=> "SELECT [lists].* FROM [lists] INNER JOIN [ListItems] ON [ListItems].[ListID] IS NULL"

When I am expecting a sql statement more like:

SELECT [lists].* FROM [lists] INNER JOIN [ListItems] ON [ListItems].[ListID] = [Lists].[ListID]

Getting me to the query above will get a correct answer. Bonus points if you can tell me how to get to something equivalent to:

SELECT [lists].*, COUNT([ListItems].*) FROM [lists] INNER JOIN [ListItems] ON [ListItems].[ListID] = [Lists].[ListID]

score 0 · Answer 1 · edited May 23 '17 at 12:04

Your first question: you probably messed with table migration. In migration file use

create_table :lists, {:id => false} do     
....
end

and then add execute "ALTER TABLE lists ADD PRIMARY KEY (listid);" And by the way, it's has_many :listitems (plural, not single).

Look here for detailed explanation: Using Rails, how can I set my primary key to not be an integer-typed column?

Second question: I would use combination of methods, not one method. YOu need to return name of list and then count name of list items in this list, right? After solving the first problem, simply use list.name and list.list_items.count

score 0 · Accepted Answer · answered Feb 03 '11 at 22:53

As usual, the answer is ridiculously simple with Rails...

My tables are named using mixed case. Adding the following line to my List model fixed it:

set_table_name "Lists"

The plurality (or lack thereof) of :listitem(s) appeared to have no effect before or after this change.

Rails 3 joins with non-standard key fields

2 Answers2