Delete "where"

Help
1

I am trying to figure the best approach to delete multiple items by ref, but only if 2 of the items’ fields equals some value.
In SQL I’d write it like this:
DELETE from MyTable WHERE id IN (id1, id2, id3) AND formId='foo' AND accountId='Bar'

Here’s what I’ve got. It feels too complicated. Is there a better way?

Query(
  Lambda(
    [
      "accountId",
      "formId",
      "submissionIds"
    ],
    Let(
      {
        submissions: 
        Filter(
          Map(
            Var("submissionIds"),
            Lambda(
              "id", 
              Get(Ref(Collection("FormSubmits"), Var("id")))
            )
          ),
          Lambda(
            "submission",
            And(
              Equals(
                Select(["data", "formId"], Var("submission")),
                Var("formId")
              ),
              Equals(
                Select(["data", "accountId"], Var("submission")),
                Var("accountId")
              )
            )
          )
        )
      },
      Map(
        Var("submissions"),
          Lambda(
          "submission",
          Delete(Select(["ref"], Var("submission")))
        )
      )
    )
  )   
)
2

Hi @danbars.

If the submissionIds list is small, it seems to me that reading the docs and filtering on them is efficient (limits read ops).

If by chance the “submissions” are coming from a different index, then you could intersect that set with an indexes with terms data.formId and data.accountId. Something like this? But that does assume a lot.

    Let(
      {
        submissions: Paginate(
          Intersection(
            Var('submissionSet'), 
            Match(Index('formSubmits_by_form_and_account'), Var('formId'), Var('accountId'))
          )
        )
      }
      Map(
        Var("submissions"),
          Lambda(
          "submission",
          Delete(Select(["ref"], Var("submission")))
        )
      )
    )

You can use most Set operations like Intersection on arrays as well, if that helps at all.

3

Thanks @ptpaterson. I didn’t know about Intersection.
Your solution looks much more concise than mine.
You mentioned read-efficiency. Can you explain how your solution will be executed in terms of read/write operations?
I have 2 main use cases -

  1. A user selects a list of submissions by checking checkboxes. In this case the list is likely to be short (what is “short” in your view btw?).
  2. A user checks “select all” in the UI. In this case the list can be long, but instead of list of specific IDs, I will just have a filter. In this case I think I’ll just paginate on a relevant index and call Lambda(Delete) .
4

The Set operations, e.g. Intersection, will only work with two Sets or two Arrays but not on both at the same time. So in case #1 (user selects a list…) the intersection method is not going to work. I think there is a complicated way to make it work, but it’s at least as expensive as just reading the documents and filtering.

Each Get will cost a Read Op, of course. Paginate costs a Read Op for each Match(Index(...)) involved. For example, Paginiate(Match(Index(...))) costs 1 read op. If you Match on two Indexes and Intersection them, that’s 2 Read Ops.

EDIT: Different Set operations work differently, and I shouldn’t generalize the pricing. You should run queries and inspect them, either in the cloud shell or headers from the web requests. Cross referencing a discussion related to the cost of Set operations.

Mapping over a single index to Delete should cost 1 read op and N write ops, where N is the number of results.

https://docs.fauna.com/fauna/current/concepts/billing

1 Like