can activerecord association with the functional foreign key

[zhijia zhang]

As we know the model can associate other model through the foreign key which is assigned or with an “_id” suffix by default. But can we define functional foreign key by ourselves ?

eg:

class Condition < ActiveRecord::Base
  belongs_to :city_info, foreign_key: "substring_index(the_city,',',1)"  
end

class CityInfo < ActiveRecord::Base
end

>> Condition.includes(:city_info).references(:city_info).last

>> Mysql2::Error: Unknown column 'conditions.substring_index(the_city,',',1)' in 'on clause': SELECT  ..... ......`conditions`.`substring_index(the_city,',',1)`  ORDER BY `conditions`.`id` DESC LIMIT 1

It's wrong. How can we achieve this.

I want to retrieve many conditions object, each associates with its first city_info by ActiveRecord with generating a single sql (not n+1

) like this:

select * from conditions left outer join city_info on 
convert(substring_index(conditions.the_city,',',1), unsigned integer) = city_info.id

ref: https://github.com/rails/rails/issues/14481