How to join two tables in Active Record by looking up non-primary-key columns from both tables?

Question

I have three tables that has the following structure:

CREATE TABLE `crawled_data` (
  `id` int NOT NULL,
  `url` longtext,
  ***OMMITTED***
  PRIMARY KEY (`id`)
);

CREATE TABLE `ingredient_frequency` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `url_id` int DEFAULT NULL,
  `url` text,
  ***OMMITTED***
  PRIMARY KEY (`id`)
)

CREATE TABLE `url_metadata` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `url` longtext,
  ***OMMITTED*** 
  PRIMARY KEY (`id`)
);

I have defined the following active record structure:

class Crawled_Data < ActiveRecord::Base
  self.table_name = "crawled_data"
  has_many :url_metadatas
end

class Ingredient_Frequency < ActiveRecord::Base
  self.table_name = "ingredient_frequency"
  belongs_to :url_metadata
end

class Url_Metadata < ActiveRecord::Base
  self.table_name = "url_metadata"
  has_many :ingredient_frequencies
  belongs_to :crawled_data
end

How should I go on joining crawled_data with url_metadata by matching the url columns (which aren't the primary keys in both tables)?

I have looked through the following threads but had no luck because the answers do not really mention how to reference the current table's column (instead only talking about referencing the foreign_key)

How to join tables with no primary key in rails?

Executing raw SQL in rails may help. See [this SO post](https://stackoverflow.com/questions/22752777/how-do-you-manually-execute-sql-commands-in-ruby-on-rails-using-nuodb) for details — obiruby, Feb 17 '21 at 04:38

sig · Answer 1 · 2021-02-17T12:40:31.587

class Crawled_Data < ActiveRecord::Base
  self.table_name = "crawled_data"
  has_many :url_metadatas, primary_key: 'url', foreign_key: 'url'
end

class Ingredient_Frequency < ActiveRecord::Base
  self.table_name = "ingredient_frequency"
  belongs_to :url_metadata, primary_key: 'url', foreign_key: 'url'
end

class Url_Metadata < ActiveRecord::Base
  self.table_name = "url_metadata"
  has_many :ingredient_frequencies, primary_key: 'url', foreign_key: 'url'
  belongs_to :crawled_data, primary_key: 'url', foreign_key: 'url'
end

and then: Crawled_Data.joins(:url_metadatas)

How to join two tables in Active Record by looking up non-primary-key columns from both tables?

1 Answers1