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 (
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 
