How to implement weird relation ship

Hi,

I’m trying to create a library providing Ecto schemas for third application. The way the database is designed is pretty weird so it makes some relationships difficult to implement. Let me give an example with two objects : pick and arrival and third one publicobject used to make the link between them :

defmodule Arrival do
use Ecto.Schema

@primary_key {:_oid, :id, autogenerate: false}

schema “arrival” do
field :m_pickid, :string
end
end

defmodule SeisComp3.Schemas.Pick do
use Ecto.Schema

@primary_key {:_oid, :id, autogenerate: false}

schema “pick” do
has_one :public_id, SeisComp3.Schemas.PublicObject, foreign_key: :_oid
end
end

defmodule PublicObject do
use Ecto.Schema

@primary_key {:_oid, :id, autogenerate: false}

schema “publicobject” do
field :m_publicid, :string
end
end

Arrival has one Pick, Pick as one PublicObject.

The problem comes from the fact store the relationship using Pick.Public.m_publicid instead of Pick._oid. So to get the pick of an arrival, the join is :

Arrival.m_pickid == PublicObject.m_publicid && PublicObject._oid == Pick._oid

So far, I don’t see how to implement this relationship with many_to_many or has_one, should I create a new function for this case or do you see an another solution ?

Thank you in advance !