What this is about
You want to find employees whose company is called “Codamic”. The name is not stored in the employee, but in the company. For this you write a path in the key: the names of the relations joined with dots, and the field at the end.
{ "key": "company.companyname", "value": "Cod%", "param": "LIKE" }flowchart LR
E["employee"] -->|"company"| C["company"]
C -->|"companyname"| V["'Codamic AG'"]
How CDMS resolves a path
-
1CDMSsplits the path at the dot:
company,companyname -
2CDMS
companyis a relation → LEFT JOIN fromemployeetocompany -
3CDMS
companynameis the field at the end → the comparison happens on it -
4CDMS→Database
… FROM employee e LEFT JOIN company c … WHERE c.companyname LIKE 'Cod%'
A path may have any number of steps, e.g. customer.address.city or order.customer.company.name. Every step is one more LEFT JOIN.
Why objects without the relation do not drop out
Example data: Daniel and Eva work at Codamic, Anna at Acme, Olaf has no company.
| Filter | Daniel (Codamic) | Eva (Codamic) | Anna (Acme) | Olaf (no company) | Meaning |
|---|---|---|---|---|---|
company.companyname LIKE Cod% | yes | yes | no | no | Olaf does not match, because he has no company name |
OR group: company.companyname LIKE Cod% / lastname EQ Ohne | yes | yes | no | yes | Olaf is included, the LEFT JOIN does not lose him |
company ISNULL | no | no | no | yes | everyone without a company |
With a normal (inner) JOIN, Olaf would have been lost in the second row, even though his last name matches. That is why CDMS always uses LEFT JOIN for filters.
All variants
When: Field of a directly referenced object.
{ "key": "company.companyname", "value": "Codamic AG", "param": "EQ" }
Result: Employees of the company “Codamic AG”.
When: The field is two or more relations away.
{ "key": "order.customer.city", "value": "Köln", "param": "EQ" } on an invoice line.
Result: One LEFT JOIN per step.
When: You know the id of the referenced object.
{ "key": "company.id", "value": "a1…", "param": "EQ" }. This is the usual form for “all employees of this company”.
Result: Comparison by ID. An invalid UUID returns 400.
When: You are looking for companies where one employee matches: { "key": "employees.lastname", "value": "M%", "param": "LIKE" }.
A path can also go through a list. The company appears once in data, no matter how many of its employees match. But totalCount counts every matching employee: two matching employees of the same company give totalCount 2 with one object in data.
Result: Companies with at least one matching employee. For “contains this one” there is MEMBEROF, which counts each company exactly once.
When: Part of the path is misspelled, e.g. firma.companyname.
CDMS does not find the step and rejects the search. It does not run at all.
Result: 400 unknown-search-key|firma.companyname.
Sorting across a path
You may also use paths in order:
"order": [ { "field": "company.companyname", "order": "ASC" } ]An unknown sort path returns 400 wrong-order-element-exception. More under Sorting.