Class SearchEngine

A tool for building up SQL queries, by adding fields, criteria, and data. The fields must be given on the following form: starttablename.fieldname, for example "ticket.title". For tables that have foreign keys to other tables, you can reach them with the following notation: starttablename.fieldname.fieldname For example, the firstname of the primary customer of a ticket is: "ticket.cust_id.firstname". The company name of the primary customer of a ticket is: "ticket.cust_id.company.name".

Make sure that all fields you use that start with the same table, for example, ticket, or else you will receive a big join that you probably do not want.

For many-to-many relations there is a special notation: table1.(table2->field2a).field2b Where field2a is a field in table2 that has a foreign key to table1.

From CS version 7, the aggregate functions are no longer supported, since the query is sent to NetServer instead of directly to the database. It is possible to circumvent this by setting the reg_id 235 to 1 and explicitly use bypassNetserver(true) in the SearchEngine. Be aware of any security implications this might cause.

Examples

ticket.(ticket_customers->ticket_id).cust_id

Constructors

SearchEngine()

Initializes a new instance of the SearchEngine class.

Declaration

SearchEngine

Methods

addComparison(String,String,String,String,Integer)

Adds a field-comparison criterion that compares 2 database fields to a database query.

You can optionally add functions to be applied to the 2 fields.

Declaration

Void addComparison(String field1, String compOperator, String field2, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field1	The field to compare to field2
String	compOperator	Comparison operator
String	field2
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addComparison(String,String,String,String,String,String,Integer)

Adds a field-comparison criterion that compares 2 database fields to a database query with functions to be applied to the 2 fields.

Declaration

Void addComparison(String field1, String func1, String compOperator, String field2, String func2, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field1	The field to compare to field2
String	func1	Optional. The function to use on field1
String	compOperator	Comparison operator
String	field2
String	func2	Optional. The function to use on field2
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addCriteria(String,String,String)

Adds a criterion on a database field to the database query

You can optionally add a row operator, priority, and function to be applied to the field.

Declaration

Void addCriteria(String field, String compOperator, String value)

Parameters

Type	Name	Description
String	field	The field to restrict
String	compOperator	Comparison operator
String	value

Returns

Type	Description
Void

addCriteria(String,String,String,String,Integer)

Adds a criterion on a database field to the database query

You can optionally add a function to be applied to the field.

Declaration

Void addCriteria(String field, String compOperator, String value, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field	The field to restrict
String	compOperator	Comparison operator
String	value
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addCriteria(String,String,String,String,String,Integer)

Adds a criterion on a database field to the database query.

Declaration

Void addCriteria(String field, String function, String compOperator, String value, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field	The field to restrict
String	function	The function to use on field
String	compOperator	Comparison operator
String	value
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addData(String,String)

Adds data for use in an update or insert query.

Declaration

Void addData(String field, String value)

Parameters

Type	Name	Description
String	field	The field to set
String	value	The value to set on the field.

Returns

Type	Description
Void

addDataField(String,String)

Adds a data field to the SearchEngine. A data field is a field assigned to another field instead of a value.

Declaration

Void addDataField(String field1, String field2)

Examples

se.addDataField("customer.phone", "customer.cellphone");

Parameters

Type	Name	Description
String	field1	Field to assign to.
String	field2	Field where the value is found.

Returns

Type	Description
Void

addDataField(String,String,String)

Adds a data field to the SearchEngine. A data field is a field assigned to another field instead of a value.

Can only be used in update() queries.

Declaration

Void addDataField(String field1, String field2, String function)

Parameters

Type	Name	Description
String	field1	Field to assign to.
String	field2	Field where the value is found.
String	function	Aggregate function to use for field2.

Returns

Type	Description
Void

addField(String)

Adds a field to the SELECT part of the database query

Declaration

Void addField(String field)

Parameters

Type	Name	Description
String	field	The field to select.

Returns

Type	Description
Void

addField(String,String)

Adds a field to the SELECT part of the database query with a function to be applied to the field.

Declaration

Void addField(String field, String function)

Parameters

Type	Name	Description
String	field	The field to select
String	function	A function to use on the field.

Returns

Type	Description
Void

addFields(String,String)

A shortcut to add multiple fields to a query. Adds all fields in the comma-separated string "fields", prefixed by "table" and a dot ".".

Declaration

Void addFields(String table, String fields)

Examples

addFields("ticket", "id,title,category.fullname,cust_id.fullName");

Parameters

Type	Name	Description
String	table
String	fields

Returns

Type	Description
Void

addHaving(String,String,String,String,Integer)

Adds a HAVING clause to the database query.

Declaration

Void addHaving(String field, String compOperator, String value, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field	The field to restrict
String	compOperator	Comparison operator
String	value
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addHaving(String,String,String,String,String,Integer)

Adds a HAVING clause to the database query.

Declaration

Void addHaving(String field, String function, String compOperator, String value, String rowOperator, Integer priority)

Parameters

Type	Name	Description
String	field	The field to restrict
String	function	The function to use on field
String	compOperator	Comparison operator
String	value
String	rowOperator	Operator for combining this and the next criterion
Integer	priority	A number. All criteria with the same number will be placed inside the same brackets.

Returns

Type	Description
Void

addJoinCriteria(String,String,String,String,Integer)

Does NOT work with NetServer. You need to bypass NetServer (see the method bypassNetServer) if you need to use this.

Declaration

Void addJoinCriteria(String p_0, String p_1, String p_2, String p_3, Integer p_4)

Parameters

Type	Name	Description
String	p_0
String	p_1
String	p_2
String	p_3
Integer	p_4

Returns

Type	Description
Void

addOrder(String,Bool)

Adds a restriction to the order on the listing of the database result.

Declaration

Void addOrder(String field, Bool ascending)

Parameters

Type	Name	Description
String	field	The field to restrict
Bool	ascending	The sort order. True = ascending, false = descending.

Returns

Type	Description
Void

addOrder(String,String,Bool)

Adds a restriction to the order on the listing of the database result.

Declaration

Void addOrder(String field, String function, Bool ascending)

Parameters

Type	Name	Description
String	field	The field to restrict
String	function	The function to use on field
Bool	ascending	The sort order. True = ascending, false = descending.

Returns

Type	Description
Void

buildSql()

Returns the SQL query generated by the SearchEngine. The query is built by addCriteria(),addComparison(),addData(), and so on.

Declaration

String buildSql()

Returns

Type	Description
String	The SQL query generated by the SearchEngine.

buildSql(String)

Returns the SQL query generated by the SearchEngine. The query is built by addCriteria(),addComparison(),addData(), and so on.

Declaration

String buildSql(String type)

Parameters

Type	Name	Description
String	type	Determines the type of query to build.

Returns

Type	Description
String	The SQL query generated by the SearchEngine.

bypassNetServer(Bool)

From version 7, all queries are sent to NetServer instead of directly to the database. This function will allow you to send the queries directly to the database instead.

Declaration

Void bypassNetServer(Bool p_0)

Parameters

Type	Name	Description
Bool	p_0

Returns

Type	Description
Void

countColumns()

Returns the count of columns (selected fields) in the SearchEngine query.

Declaration

Integer countColumns()

Returns

Type	Description
Integer	The count of columns (selected fields) in the SearchEngine query.

countRows()

Returns the number of rows in the result set.

Declaration

Integer countRows()

Returns

Type	Description
Integer	The number of rows in the result set.

delete()

After adding criteria, call this to delete the items that was selected by the query.

Declaration

Void delete()

Returns

Type	Description
Void

eof()

Checks if the end of the result set is reached.

Declaration

Bool eof()

Returns

Type	Description
Bool	True if the end of the result set is reached; otherwise, false.

execute()

Executes the query. Must be called to get the results.

Declaration

Integer execute()

Returns

Type	Description
Integer

executeHTMLTable()

Executes the query and return the result as a HTML formatted table.

Declaration

String executeHTMLTable()

Returns

Type	Description
String	The result as a HTML formatted table.

executeInto(StringMatrix)

Executes the query and returns the result in the matrix.

Declaration

Void executeInto(StringMatrix matrix)

Parameters

Type	Name	Description
StringMatrix	matrix	The matrix to fill with the result set.

Returns

Type	Description
Void

executeJSON()

Executes the query and return the result as a JSON formatted text.

Declaration

String executeJSON()

Returns

Type	Description
String

executeTextTable()

Executes the query and return the result as a text formatted table (similar to MySQL).

Declaration

String executeTextTable()

Returns

Type	Description
String

executeToJSONBuilder(JSONBuilder,String,String)

Executes the SearchEngine to a JSONBuilder.

The fields string can contain a comma-separated list of label:fieldType elements, allowing you to better control the resulting json. The label is the name the field gets in the json object. The fieldType can be "Integer", "Float", "Bool", or "String" (default). The json object member will be formatted accordingly, e.g. a Bool will be "true" or "false", while Integer/Float will become unquoted numbers.

The arrayName will become the name of the array in the JSONBuilder.

Declaration

Void executeToJSONBuilder(JSONBuilder jb, String fields, String arrayName)

Examples

SearchEngine se;
se.addFields("sale", "sale_id,heading,amount");
se.setLimit(10);
JSONBuilder jb;
jb.setPrettyPrint(4);
jb.pushObject("");
jb.addString("foo", "bar");
se.executeToJSONBuilder(jb, "id:Integer,heading:String,amount:Float", "sales");
jb.popLevel();
printLine(jb.getString());

Parameters

Type	Name	Description
JSONBuilder	jb
String	fields
String	arrayName

Returns

Type	Description
Void

findAlternativeFields(String)

Finds all alternative fields given a start field.

These fields can be used in addField() or similar functions.

Declaration

String[] findAlternativeFields(String fieldBeginsWith)

Parameters

Type	Name	Description
String	fieldBeginsWith	Fields that begins with...

Returns

Type	Description
String[]

findRelations(String,Bool)

Declaration

String[] findRelations(String p_0, Bool p_1)

Parameters

Type	Name	Description
String	p_0
Bool	p_1

Returns

Type	Description
String[]

first()

Sets the row pointer to the first item of the result set.

Declaration

Bool first()

Returns

Type	Description
Bool	False if the result set is empty, otherwise, true.

getField(Integer)

Returns the value of a field.

Declaration

String getField(Integer num)

Parameters

Type	Name	Description
Integer	num	The number of the field. Starting at 0. Alternative to using name.

Returns

Type	Description
String	The value of the field.

getField(String)

Returns the value of a field.

Declaration

String getField(String name)

Parameters

Type	Name	Description
String	name	The name of the field. On the form table.fieldname. Alternative to using num.

Returns

Type	Description
String	The value of the field.

getField(String,String)

Returns the value of a field.

Declaration

String getField(String name, String function)

Parameters

Type	Name	Description
String	name	The name of the field. On the form table.fieldname. Alternative to using num
String	function	The function to use on the field. Used in combination with name.

Returns

Type	Description
String	The value of the field.

getFieldsJSON()

Returns fields as JSON formatted string.

Declaration

String getFieldsJSON()

Returns

Type	Description
String

insert()

After adding the values of the fields, call this function to actually do the insert in the database. Returns the resulting ID of the insert.

Declaration

Integer insert()

Returns

Type	Description
Integer	The ID of the newly added item.

next()

If not the end of the result set is reached, the result pointer is set to the next line.

Declaration

Bool next()

Returns

Type	Description
Bool	False if the end of result set is reached; otherwise, true.

select()

Does the select-query and returns the size of the result set.

Declaration

Integer select()

Returns

Type	Description
Integer	The number of items in the result set.

setCallBack(String)

Sets a CRMScript function as a callback that will be used to convert the values before they are displayed in the grid.

Declaration

Void setCallBack(String functionName)

Parameters

Type	Name	Description
String	functionName	Name of ejscript function.

Returns

Type	Description
Void

setDBDistinct(Bool)

Forces SearchEngine to use a distinct in the query.

Declaration

Void setDBDistinct(Bool p_0)

Parameters

Type	Name	Description
Bool	p_0

Returns

Type	Description
Void

setDistinct(String)

Makes the entries distinct so that equal tickets will not be listed.

Declaration

Void setDistinct(String field)

Examples

se.setDistinct("ticked.id"); There will only be one entry with the same ticket ID, very useful with many to many relations.

Parameters

Type	Name	Description
String	field

Returns

Type	Description
Void

setGroup(Bool)

Sets or removes a group-clause to all of the fields in the query (added by the addField() function).

Declaration

Void setGroup(Bool set)

Parameters

Type	Name	Description
Bool	set	True if you want to set the groupby-clause, false to remove a groupBy-clause.

Returns

Type	Description
Void

setLimit(Integer)

Sets the limit of number of rows to receive in the result set.

Declaration

Void SetLimit(Integer number)

Parameters

Type	Name	Description
Integer	number	The limit of the rows to receive in the result set.

Returns

Type	Description
Void

update()

Use this function after addData(), which sets the values that will be modified, and addCriteria(), which do a restriction on the items to modify.

Declaration

Void update()

Returns

Type	Description
Void