querying another model in a rails model function-CodePudding

Basically, what I'm trying to do is a join on a text field rather than an id like

SELECT country.delivery_time 
FROM order, country
WHERE order.country = country.name

and what I've got so far for my Rails models is

class Country < ApplicationRecord
  validates :name, presence: true
  validates :delivery_time, presence: true
end

class Order < ApplicationRecord
  validates :country, presence: true

  def shipping_time
    # want to get the correct country from the DB
    if self.country == country.name
      country.delivery_time
    else
      "unavailable"
    end
  end
end

where shipping_time is the function that should return the result of the join. The error I'm getting is

undefined method `country'

I'm using Ruby 3.0.3 and Rails 7.0.0.

CodePudding user response：

Breaking the conventions and using a foreign key column of any type is actually pretty straight forward:

class AddCountryNameToOrders < ActiveRecord::Migration[7.0]
  def change
    add_column :orders, :country_name, :string
    # Some DB's may not allow foreign key constraints 
    # on non PK columns
    add_foreign_key :orders, :countries,
      column: :country_name,
      primary_key: :name
  end
end

class Order < ApplicationRecord
  validates :country, presence: true
  belongs_to :country, 
    foreign_key: :country_name,
    primary_key: :name
end

class Country < ApplicationRecord
  has_many :orders,
    foreign_key: :country_name,
    primary_key: :name
end

The column used as the primary_key option doesn't actually have to be the PK of the target table. Simply naming the column country would cause a name clash though unless you choose different name for the assocation (and make this into even more of a mess).

But given the domain its a very bad idea.

Countries do actually change names - recent examples are North Macedonia and Eswatini.

Instead just use a normal integer/uuid foreign key column and use delegation to get the name from the country.

class Order < ApplicationRecord
  belongs_to :country
  delegate :name, to: :country,
                  prefix: true
end

If you want to use a "natural" primary key for the table instead of a surrogate key (such as an autogenerated id) then the ISO 3166 country codes are a far better choice.

CodePudding user response：

So I ended up using the joins function to get what I wanted,

Order.joins("INNER JOIN countries ON orders.country = countries.name")

where the full function is

def shipping_time
  relation = Order.joins("INNER JOIN countries ON orders.country = countries.name")
  if 1 == relation.count()
    relation[0].delivery_time
  else
    "unavailable"
  end
end