Ecto: how to insert into a Schema with one field being a subquery

Hey everyone, just joined this forum. I’m new to Elixir and have been working on a side project to learn it. Happy to be here.

I have a question related to Ecto, and I’m not sure if what I’m trying to do makes sense.

I want to insert rows in a SQL table from an Ecto.Changeset that will contain some fields provided by the user, and I want one of the fields not to be provided by the user, but to be the result of a query instead.

Some Background

I’m writing a Phoenix application. It has a SQL database (I’m using SQLite), and there are two related Schemas: a “Parent” has_many “Children” (so “Child” belongs_to “Parent”, it has a parent_id FK).

I want the children table to have a column named parent_scoped_row_id, which would store the row number of that child scoped by parent_id. Here’s an example of what I mean:

I want parent_scoped_row_id to not be provided by users, but auto-generated instead.

SQL solution

The following works on SQLite:

create table parents(id primary key);
create table children(
  id primary key,
  parent_id integer not null constraint "child_parent_fk" references parents(id),
  inserted_on text,
  parent_scoped_row_id integer
);
insert into parents values (1), (2), (3);
insert into children values (
  1,
  1,
  "2023-12-01",
  (select 1 + coalesce(max(parent_scoped_row_id), 0) from children where parent_id = 1)
);
insert into children values (
  2,
  2,
  "2023-12-01",
  (select 1 + coalesce(max(parent_scoped_row_id), 0) from children where parent_id = 2)
);
-- and so on...

Problem is: how can I reproduce it with Ecto?

What I’ve tried

# child.ex
defmodule Myapp.Example.Child do
  use Ecto.Schema
  import Ecto.Changeset

  alias Myapp.Example.Parent

  schema "children" do
    field :inserted_on, :date
    field :parent_scoped_row_id, :integer

    belongs_to :parent, Parent
  end

  @doc false
  def changeset(child, attrs) do
    child
    |> cast(attrs, [:inserted_on, :parent_id])
    |> validate_required([:inserted_on, :parent_id])
  end
end

# application code
defmodule Example
  def create_child(attrs \\ %{}) do
    changeset =
      %Child{},
      |> Child.changeset(attrs)

    parent_id = get_field(changeset, :parent_id)

    subquery =
      from(
        c in Child,
        where: c.parent_id == ^parent_id,
        select: 1 + coalesce(max(c.parent_scoped_row_id), 0)
      )

    changeset
    |> put_change(:parent_scoped_row_id, subquery)
    |> Repo.insert()
  end
end

But when I run create_child(), I get this error:

** (Ecto.ChangeError) value `#Ecto.Query<from c0 in Myapp.Example.Child, where: c0.parent_id == ^1, select: 1 + coalesce(max(c0.parent_scoped_row_id), 0)>` for `Myapp.Example.Child.parent_scoped_row_id` in `insert` does not match type :integer

I also tried using other types for :parent_scoped_row_id, and also not declaring it altogether but none of those worked.

I could probably solve this by getting the field and then putting it in the changeset before inserting, all inside a transaction. Not sure this would work, but I really wanted to try solving it with a single SQL statement before going this path.

What now?

I’m not sure where to go from here. I suppose I could try to build a Custom Field Type instead of :integer for my field, or try Ecto’s autogenerate, or something else. But as I said, I’m new to all of this and I don’t know how autogenerate and Custom Field Types work. I also don’t know if those are the right solution for the problem.

Does anyone have any insights that could help me?

Thank you for your patience reading all this