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:

id parent_id inserted_on parent_scoped_row_id
1 1 2023-12-01 1
2 1 2023-12-02 2
3 1 2023-12-03 3
4 2 2023-12-01 1
5 2 2023-12-02 2
6 3 2023-12-01 1

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 (
  (select 1 + coalesce(max(parent_scoped_row_id), 0) from children where parent_id = 1)
insert into children values (
  (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

  @doc false
  def changeset(child, attrs) do
    |> cast(attrs, [:inserted_on, :parent_id])
    |> validate_required([:inserted_on, :parent_id])
# application code
defmodule Example
  def create_child(attrs \\ %{}) do
    changeset =
      |> Child.changeset(attrs)

    parent_id = get_field(changeset, :parent_id)

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

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

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 :sweat_smile:

insert_all will allow fields to be populated by a subquery


Awesome to know! I’ll try that, thank you so much.

No problem. The issue with plain insert is that it expects values so it can perform validation prior to persisting to the database.

1 Like

So, is this code right? I’m getting an error and it seems like Ecto is not sending the subquery as the value for the field as I expected. Am I doing something wrong?

iex(1)> subquery = from(
...(1)>   p in Child,
...(1)>   where: p.parent_id == 1,
...(1)>   select: (1 + coalesce(max(p.parent_scoped_row_id), 0))
...(1)> )
#Ecto.Query<from c0 in Myapp.Example.Child, where: c0.parent_id == 1,
 select: 1 + coalesce(max(c0.parent_scoped_row_id), 0)>
iex(3)> Repo.insert_all(
...(3)>   Child,
...(3)>   [%{
...(3)>     inserted_on: ~D[2023-01-01],
...(3)>     parent_id: 1,
...(3)>     parent_scoped_row_id: subquery,
...(3)>   }]
...(3)> )
[debug] QUERY ERROR db=2.3ms idle=580.4ms
INSERT INTO "children" ("parent_id","parent_scoped_row_id","inserted_on") VALUES (?,?,?) [1, ~D[2023-01-01]]
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:312
** (Exqlite.Error) arguments_wrong_length
INSERT INTO "children" ("parent_id","parent_scoped_row_id","inserted_on") VALUES (?,?,?)
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:918: Ecto.Adapters.SQL.insert_all/9
    (ecto 3.10.3) lib/ecto/repo/schema.ex:59: Ecto.Repo.Schema.do_insert_all/7
    iex:3: (file)

It seems like the SQLite adapter does not handle query values at the moment. It is turning it into a query parameter. Take a look at the difference between the Postgres and SQLite adapter for this function:



My advice would be to open an issue for the sqlite adapter and see if it’s possible for them to support it.


@joey_the_snake thank you so much for your help! I’ll reach out to them. In the meantime, I guess I’ll just go with opening a transaction, then querying the value I want from the DB, and them inserting the new record.

Seems like @greg-rychlewski beat me and committed the change even before I finished writing the Issue on Github.

That was awesome! Thank you so much :grin: :tada: