Ecto, Updating all children in a limitless nested recursive association

Phoenix Forum Questions / Help
, ,
1

Hello, I’m not good at algorithm and I’ve ran into this problem.

I have these tables folders, and files

Folder

schema "folders" do
  field :name, :string
  field :archived, :boolean

  belongs_to :folder, Folder
  has_many :folders, Folder
  has_many :files, File
end

File

schema "files" do
  field :name, :string
  field :archived, :boolean

  belongs_to :folder, Folder
end

The archived column is used when soft-deleting folders and files.

A parent Folder can have unlimited children folders and files.

FolderA
 |-file1
 |-file2
 |-folder_b
    |-file1
    |-folder_c
       |-file1
       |-folder_d
         ...
         # NO_LIMIT

Now, what I need to do is, If I soft-delete FolderA, All it’s children folders and files down the line should be also be soft-deleted (updated archived value into true).

How do I accomplish this in Ecto?

2

You could do something like that:

def soft_delete_folder(folder) do
    folder
    |> Repo.update_all(set: [archived: true])
    |> case do
      {:ok, _updated_folders} ->
        # Soft deletes all folders in the folder
        folder
        |> Repo.preload(:folders)
        |> Map.get(:folders)
        |> Enum.each(&soft_delete_folder/1)

        # Updates all files in the folder
        Repo.update_all(from f in File, where: f.folder_id == ^folder.id, set: [archived: true])
      
      _ ->
        {:error, "Failed to soft delete folder and children."}
    end
  end

And call it like this:

folder = Repo.get(Folder, folder_id)
soft_delete_folder(folder)
1 Like
3

A recursive CTE with update_all should do it. If you need validation and whatnot, then you’d have to do something like @igsp7 suggested.

4 Likes
4

Somewhat of a hybrid and probably not really optimal, but it could be a start:

def delete_folder(%Folder{id: id}) do
    folder_ids_query = """
    WITH RECURSIVE folder_ids(id, folder_id) AS (
      SELECT id, folder_id FROM folders WHERE id = $1
      UNION ALL
      SELECT t.id, t.folder_id FROM folders t INNER JOIN folder_ids c on c.id = t.folder_id
    )
    SELECT id FROM folder_ids
    """

    {:ok, result} = Repo.query(folder_ids_query, [id])
    folder_ids = List.flatten(result.rows)

    Repo.transaction(fn ->
      from(f in Folder, where: f.id in ^folder_ids, update: [set: [archived: true]])
      |> Repo.update_all([])

      from(f in File, where: f.folder_id in ^folder_ids, update: [set: [archived: true]])
      |> Repo.update_all([])
    end)
  end
3 Likes