Searching collections with MEMBEROF

What this is about

A company has a list employees. You have an employee and want to know: In which companies is he on the list? With n:m relations, such as users and groups, this can be more than one.

{ "key": "employees", "value": "7f3…", "param": "MEMBEROF" }

The set diagram

flowchart LR
    subgraph A["Company Codamic"]
        a1["Daniel 7f3…"]
        a2["Eva 2c9…"]
    end
    subgraph B["Company Acme"]
        b1["Anna 8b1…"]
    end
    Q["employees MEMBEROF 7f3…"] -.->|"contains Daniel"| A
    Q -.->|"does not contain Daniel"| B

Result: only “Codamic”.

How CDMS asks the question

CDMS does not join the list to the main query. Instead, it asks the database a separate yes/no question for each object (EXISTS):

SELECT … FROM company c
WHERE EXISTS (SELECT 1 FROM c.employees m WHERE m.id = '7f3…')

This has two effects:

A company appears exactly once, and totalCount is correct.
Companies without employees stay in the search if another condition of an OR group matches them.

All variants

MEMBEROF in all forms

by IDin an OR groupnot a list fieldinvalid UUID

When: The list contains objects (references), e.g. employees, roles, groups.

value is the id of the object you are looking for: { "key": "employees", "value": "7f3…", "param": "MEMBEROF" }.

Result: All objects whose list contains this object.

When: “Companies with Daniel or companies named Acme”

{ "type": "OR", "filter": [ { "key": "employees", "value": "7f3…", "param": "MEMBEROF" }, { "key": "companyname", "value": "Acme", "param": "EQ" } ] }

Result: Codamic and Acme, each once.

When: { "key": "company", "value": "a1…", "param": "MEMBEROF" } on employee, but company is a single reference.

1

CDMS
checks whether company is a list
2

CDMS→Client
no → 400 memberof-needs-collection|company

Result: For single references you use { "key": "company.id", "value": "a1…", "param": "EQ" }.

When: A list of objects, but value is not a UUID, e.g. "xyz".

CDMS cannot read the value as an ID.

Result: 400 wrong-uuid-in-where|xyz

MEMBEROF or path?

Two ways to search a list

MEMBEROF

employees MEMBEROF 7f3…

checks one specific object by its id
each object at most once, totalCount is correct
works in OR groups without surprises

Path

employees.lastname LIKE M%

compares a field of the list entries with any operator
an object appears once in data
totalCount counts every matching list entry, see Filtering and sorting across relations