API/Queries/Query-Search-Options.md

+[[Queries]] [[Query-Examples]]
+
+[TOC]
+
+# NULL /  NOT NULL
+
+To search for (not) NULL entries, use the null value in json:
+
+```JSON
+#/data/extensions/widget/query
+
+{  "conds" : {"me.query" : null }}
+```
+
+```JSON
+#/data/extensions/widget/query
+
+"conds" : {
+  "me.query" : {
+      "-not" : null
+    }
+  }
+```
+
+# IDENT - comparing two columns
+
+```JSON
+#/data/system/entity/query
+
+{ "conds" : {"me.name" : {"-ident": "module.name"}} }
+```
+
+```SQL
+WHERE ( me.name = module.name )
+```
+
+<http://search.cpan.org/~ilmari/SQL-Abstract-1.84/lib/SQL/Abstract.pm#-ident>
+
+# LIKE and ILIKE
+
+```JSON
+#/data/system/entity/query
+{ "conds" : {"me.name" : {"-like": "%a%"}} }
+{ "conds" : {"me.name" : {"-not_like": "%a%"}} }
+{ "conds" : {"me.name" : {"-ilike": "%a%"}} }
+{ "conds" : {"me.name" : {"-not_ilike": "%a%"}} }
+```
+
+```SQL
+WHERE ( me.name like ? )
+WHERE ( me.name not like ? )
+```
+# (not) IN
+
+```JSON
+#/data/system/entity/query
+{ "conds" : {"me.name" : {"-in": [1,2]}} }
+{ "conds" : {"me.name" : {"-not_in": [3,4]}} }
+```
+
+```SQL
+WHERE ( me.name in (?) )
+WHERE ( me.name not in (?) )
+```
+
+# Regex
+
+The regex operators (`~`, `!~`, `~*`, `!~*`) are supported:
+
+```JSON
+#/data/system/entity/query
+
+"conds" : {"me.name" : {"~*": "module.name"}}
+```
+
+```SQL
+WHERE ( me.name ~* ? )
+```
+
+If a column on a big table is not indexed correctly, this search option will not be allowed.
+
+# Similarity
+
+This is an extension for IronAPI.
+
+
+```JSON
+{"attrs" : {"order_by" : {"-similarity" : ["me.name","constant"]}, "columns" : "me.name"}}
+```
+
+This will be translated to sql `ORDER BY me.name <-> ? : "constant"`.
+
+If a column on a big table is not indexed correctly, this search option will not be allowed.
+
+# tsquery
+
+This is an extension for IronAPI. The attrs are just for reference, they are not needed for the search query!
+
+```JSON
+#/data/system/entity/query
+{
+  "conds" : {"-tsquery" : ["english","me.name","data"] },
+  "attrs" : {"+select" : "to_tsquery('english',me.name)", "+as" : "tsquery_name"}
+}
+```
+
+will be translated to
+
+```SQL
+  WHERE ( to_tsvector(?,me.name) @@ to_tsquery(?, ? ) ): 'english', 'english', 'data'
+```
+
+If a column on a big table is not indexed correctly, this search option will not be allowed.
\ No newline at end of file