When performing bulk inserts where nested items (e.g., movies in a character insert) might conflict with each other within the same batch, a simple .unlessConflict on the main insert is insufficient because it only handles conflicts with data existing before the query started.
To handle intra-query conflicts:
- Extract and De-duplicate: Use
e.op("distinct", ...) on the nested items to ensure you only attempt to insert unique values. - Use
e.with: Break the nested insertion into its own top-level query block using e.with. This allows you to perform the de-duplication and insertion of the nested items first, then reference the resulting set when inserting the parent items. - Use
unlessConflict on the inner insert: This handles conflicts with data already in the database.
const query = e.params(
{
characters: e.array(
e.tuple({
portrayed_by: e.str,
name: e.str,
movies: e.array(e.str),
})
),
},
(params) => {
// 1. Create a de-duplicated set of movies to insert first
const movies = e.for(
e.op(
"distinct",
e.array_unpack(e.array_unpack(params.characters).movies)
),
(movieTitle) => {
return e
.insert(e.Movie, { title: movieTitle })
.unlessConflict((movie) => ({
on: movie.title,
else: movie,
}));
}
);
// 2. Use e.with to scope the movie insertion at the top level
return e.with(
[movies],
e.for(e.array_unpack(params.characters), (character) => {
return e.insert(e.Character, {
name: character.name,
portrayed_by: character.portrayed_by,
movies: e.assert_distinct(
e.select(movies, (movie) => ({
filter: e.op(movie.title, "in", e.array_unpack(character.movies)),
}))
),
});
})
);
}
);
