Rails join without primary key

Question

I have a date_dimensions table with dates that has a PK for every day. I also have a reports table with a month_year as the only date. I need to do a join query that joins date_dimensions to reports. The only use of this relationship in the app will be to run this query.

How would I set up this relationship? Or can I just write a raw query to join the two?

date_dimensions:

Model:

class DateDimension < ActiveRecord::Base
    has_many :reports
end

Migration:

class CreateDateDimensions < ActiveRecord::Migration
  def change
    create_table :date_dimensions do |t|
      t.date :date
      t.integer :month
      t.string :year_month_name
      t.integer :year
    end
  end
end

reports:

Model:

class Report < ActiveRecord::Base
    belongs_to :date_dimension
end

Migration:

class CreateReports < ActiveRecord::Migration
  def change
    create_table :reports do |t|
      t.string :customer_name
      t.string :month_year
      t.integer :page_views
    end
  end
end

Answer 1

You can do something like:

DateDimension.joins('INNER JOIN reports ON date_dimensions.year_month_name = reports.month_year')

Although your names indicate that they don't have the year and month in the same order so that might not work.

Don't forget to add indexes for both of those join columns.

Update 1

Sorry, I missed that you weren't using this elsewhere. You could set up the relationship as:

`has_many :reports, primary_key: 'year_month_name', foreign_key: 'month_year'`

then you should be able to use standard Rails syntax to access the tables