Ecto delete a record WITHOUT selecting first

Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? https://hexdocs.pm/ecto/Ecto.Repo.html#c:delete/2 has examples, but they are all doing 2 operations: a select, then a delete.

In the interest of optimization, is it possible with Ecto to delete a record with a single operation? Something like the SQL query DELETE FROM my_table WHERE id=123 ?

Is it considered bad form to delete without selecting first?

Thanks for pointers, as always.

1 Like

Ypu can do something like this:

%MyStruct{id: 10} |> MyApp.Repo.delete

That is just build a struct with the id in it then delete

2 Likes

Can also look at repo.delete_all/2, it accepts a query for doing just that. :slight_smile:

4 Likes

from(x in DataModel, where: x.id == ^id) |> Repo.delete_all

9 Likes

I tried that, but that generates an error:
** (Ecto.StaleEntryError) attempted to delete a stale struct

1 Like

I think that will only do that if the id you gave it doesn’t exist in the db… maybe it was already deleted?

From the docs:

If the struct has been removed from db prior to call, Ecto.StaleEntryError will be raised.

So yep, looks like you are right. :slight_smile:

Thank you! Just to show a more thorough example:

{rows_deleted, _} = from(x in DataModel, where: x.id == ^id) |>; Repo.delete_all

You might get zero as the number of rows deleted.

1 Like

I think you have to do a query to pass existing rows to delete or delete_all,
because they require the Ecto metadata element of the struct to contain the ‘loaded’ flag.
The only alternative would be to completely fake the whole struct,
including all the internal fields and flags.

But I agree with you that there should be a version of delete,
which just takes a primary key value, by analogy with the ‘get’ function.

1 Like