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
I have a child table called
child with ecto schema
Child and a field
args of type
args is a json object with the following structrure:
parent_id, <- this points to the primary key of "parent"
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")
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!
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.
Thanks for your answer and yeah, excited to be here
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
defmodule Parent do
schema "parent" do
has_many :parent_child, ParentChild
has_many :children, Child, through: [:parent_child, :child]
That would solve the problem I believe, albeit at the cost of one additional table. I will take this idea for a spin soon.