Transitive unique index with multiple terms

I have the following index:

{
  ...index,
  terms: [
    { field: ["data", "test1"] },
    { field: ["data", "test2"] }
  ],
  unique: true
}

but when I create the following data:

{
  test1: 1
  test2: 2
}

and then created the following data:

{
  test1: 2,
  test2: 1
}

They are not being considered as duplicates and will still be created. I want it so that they are “transitive” regardless of position, they should still be duplicates.

A workaround that I can see is to create another index but this time switch the terms so that you have two unique indexes of the same terms but different positions.

It would be good if we can have an optional option called transitive which will be defined together with unique, a boolean to indicate if we want the terms to be unique regardless of their position.

EDIT 1: I just tried this workaround, for unknown reasons, it does not work.

You could likely create a binding that uses some function that returns A+B such that A+B always equals B+A (this is in abstract sense, not actual arthmatic)

perhaps create a string of the two values sorted and separated by some delimiter? So with values 2 and 1, the binding should come out to "1_2". or with values 42 and 3.14, "3.14_42". Provide this binding as either a Value or a Term but do NOT include either of the original fields.

Also I don’t believe the order of the terms should have any affect whatsoever as the uniqueness of the index. Like, a==1 && b==2 is the same as saying b==2 && a==1. Only difference will be the order you enter them when calling Match.

Provide this binding as either a Value or a Term but do NOT include either of the original fields.

I’m intrigued. Can you direct me to the docs that explain how I can do that? is that something built-in to indexes? It so, that would be fantastic!

Also I don’t believe the order of the terms should have any affect whatsoever as the uniqueness of the index. Like, a==1 && b==2

Yes, it does. example would be like a social network, if you have a table called “friend_requests” and you only have entries there where April sent a friend request to ptpaterson, what you would have would be a table where there are two fields that when combine together defines the uniqueness of the row, that is you can’t have two records where the first is A and B then the second is B and A. Though you can do that programmatically, I think it’s best to have that rule on the database as well.

Bindings are in the docs for indexes.

No it doesn’t. lol :slight_smile: What I mean is that it doesn’t matter to the DB. It matters a great deal to your data model that these two things are interchangeable, yes! I get that. But necessarily, the data has two different fields (test1 and test2 in your example). The DB will always (and should) treat them as distinct. There is no reason why Fauna should make any assumption that they are interchangeable. But luckily custom index bindings are super flexible and powerful.