Foreign key in jsonb field for ecto association

Questions / Help
, ,
1

Hello :wave:,

I’m trying to define a one-to-many association where the child’s foreign key is contained inside a jsonb field.
I have a parent table (let’s just call it parent) with an integer primary key field id.
I have a child table called child with ecto schema Child and a field args of type jsonb.
The field args is a json object with the following structrure:

{
  parent_id, <- this points to the primary key of "parent"
  ...
  <other_fields>
}

I would like to define a has_many association on the parent’s schema, something along these lines:

schema "parent" do 

  has_many :children, Child, foreign_key: fragment("(args->>'parent_id')::int")
end

I’m aware that this won’t work in Ecto, but it will hopefully convey what I’m trying to achieve.

I’m also aware that I could change the child’s table’s schema to add an ordinary foreign key, but it’s something I would like to avoid because the table and its schema are managed by an external library. Also, I don’t want to clutter the table with ad-hoc keys for this specific use case, as the same table will be used in other scenarios.

My workaround would have been to manage the children field myself without Ecto. That would of course work.

However, before I go down that path, I wanted to ask if there is a way to achieve this directly with Ecto. I suspect that this is not possible, but perhaps someone here has some suggestions on how to “trick” Ecto into recognizing this kind of association.

Thank you very much in advance!

1 Like
2

Hello and welcome,

My first thought would be to use a parent_id field to Child, but not inside jsonb.

But maybe giving some shape to this jsonb with the help of embeded schema will allow the use of normal associations.

3

Thanks for your answer and yeah, excited to be here :slight_smile:

As I mentioned, I know that I could add perent_id to Child, but I don’t want to do it because I don’t manage the child table or its schema. The child table is a general purpose table managed by a different library.

I can’t change the Child schema either, for the very same reason.

That said, if I could change the Child schema, your suggestion of “giving shape” to the jsonb field via an embedded schema would be an interesting one.
However, I doubt that it would work, because the foreign_key option in the has_many definition in the parent table requires a top-level field in the child schema. How would I pass a field in an embedded schema to it?

I thought about this a bit more yesterday and I believe that a decent way to solve this in Ecto is to use a has_many through association (the Child table does have primary keys). So I could create a parent_child join table with following schema:

defmodule ParentChild do

  schema "parent_child" do 
     belongs_to :parent, Parent
     belongs_to :child, Child
  end
end

and then:

defmodule Parent do
  schema "parent" do 
    has_many :parent_child, ParentChild

    has_many :children, Child, through: [:parent_child, :child] 
  end
end

That would solve the problem I believe, albeit at the cost of one additional table. I will take this idea for a spin soon.