Making a belongs_to with substring as foreign key?

Questions / Help
1

Hi,

I have two related entities : Phones and TACs. TACs represent phone models, they are identified by a TAC number which is an 8 digits string. Phones are identified by an IMEI number which is a 15-16 digits string. The first 8 chars of an IMEI number are always a TAC number.

I would like to be able to do something like that so that I can load my phones with their tac data.

schema "tacs" do
  field(:tac, :string) # exactly 8 chars
  field(:model_name, :string)
  [...]
end

schema "mobile_phones" do
  field(:imei, :string) # 15 or 16 chars
  [...]
  belongs_to(:tacs, Tac, foreign_key: [... the first 8 chars of field :imei ...])
end

I would like to avoid making a DB view. How could I tackle that problem in pure Elixir ?

2

I’m wondering if this can be done with sql in the first place. You can build a changeset to make sure the foreign key is always the first 8 chars of :imei, but I doubt it could be one field and have the substring part be done by the db.

3

Hmm… Actually I’m not at all interested by ensuring DB consistency (my imeis and tacs are given by external sources that I’m forced to trust anyway and they are not guaranteed to be consistent at any time)

My question was about querying : I would like to get the tac preloaded easily when loading a mobile phones from db.