Changes between Version 51 and Version 52 of S3/S3REST/URLFormat

Timestamp:

04/09/20 13:17:32 (5 years ago)

Author:

Fran Boon

Comment:

Example of doing a negative component search by creating a custom component

S3/S3REST/URLFormat

@@ -112 +112 @@
 
 Find persons whose first name starts with "Mir":
-
 {{{
 /pr/person?person.first_name__like=Mir*
@@ -118 +117 @@
 
 The tilde {{{~}}} refers to the master resource addressed by the URL, i.e. this one is equivalent to the former:
-
 {{{
 /pr/person?~.first_name__like=Mir*
@@ -124 +122 @@
 
 Find offices with location names which start with "Osl":
-
 {{{
 /org/office?~.location_id$name__like=Osl*
@@ -130 +127 @@
 
 Query operators can be negated by inserting a {{{!}}}, i.e. find offices with location names which do ''not'' start with "Osl":
-
 {{{
 /org/office?~.location_id$name__like!=Osl*
 }}}
+
+Find all people *without* a specific [set of] qualification(s):
+
+This isn't supported out of the box, as it cannot be done with WHERE clause(s). However it is easy to do it with a little custom code:
+{{{
+def customise_pr_person_resource(r, tablename):
+    # Filtered Component to allow an exclusive filter
+    s3db = current.s3db
+    ctable = s3db.hrm_certificate
+    query = (ctable.name.like("Fire%")) & \
+            (ctable.deleted == False)
+    rows = current.db(query).select(stable.id)
+    fire_cert_ids = [row.id for row in rows]
+    s3db.add_components("pr_person",
+                        hrm_certification = {"name": "missing_qualification",
+                                             "joinby": "person_id",
+                                             "filterby": {"certificate_id": fire_cert_ids},
+                                             },
+                        )
+settings.customise_pr_person_resource = customise_pr_person_resource
+}}}
+
+Then you can do:
+{{{
+/pr/person?missing_qualification.id=None*
+}}}
+(This can be then further refined using standard UI Filter Widgets)
 
 == Bracketed OR Queries ==