I have a parent entity, which has_many children subentities:
has_many :children, PhxApp.Parent.Child, on_replace: :delete
Children are stored along with the parent and come from inputs_for
equipped HTML form. Storing the parent record inserts all the children one by one, in the order they come from the form. This allows me to simply ORDER BY
children id
s on retrieval / preload to have them correctly (in the order they were created in the form) ordered.
But… sending potentially dozens of INSERT
s to store associated records seems “suboptimal”. So the question is: am I doing something wrong that Ecto
issues new INSERT
for each associated record? I build a regular changeset
where I cast_assoc(:children)
giving it a list of children’s changesets
. Something like here (simplified):
def changeset(parent, attrs \\ %{}) do
parent
|> cast(attrs, [:identifier, :notes, ...])
|> cast_assoc(:children)
|> <some validations follow here>
end
This is the standard approach; multiple INSERT
s are preferable for both Ecto reasons (insert_all
doesn’t fill autogenerate
columns, for instance) and for database reasons (IIRC precisely identifying constraint errors on a multiple-row insert are tricky)
1 Like
I see. I was kind of expecting it to use a single INSERT
but now that you wrote it I recall other frameworks also using separate INSERT
s in equivalent situations (and me wondering about it
). So there’s probably something on the DB level as you mention. Even though my gut feeling is that there shouldn’t be a problem. And it generally feels like a “reverse” N+1. But maybe it’s the lowest denominator across multiple supported RDBMSs. TNX.
There won’t be individual constraint errors on a insert inserting many records. You’ll just know something cause the issue, but not which record.
1 Like
True that. Yet I can imagine situations where I would gladly trade that off.
Anyway, since this is “standard approach”, rather than me doing something wrong, then I’ll start bothering about optimising it once I run into N > {a reasonable number}. TNX guys.
P. S. I thought about the lowest common denominator because I recall that the multiple rows insert construct wasn’t “always” present/supported across all RDBMSs/SQL variants/extensions. I remember being positively surprised once I learnt about it being supported in (don’t remember anymore which RDBMS)
some good years ago. So it theoretically could be that something out of the Ecto
supported ones doesn’t allow that at all? Although as of today chances for that are pretty slim I guess.