Modifying the SQL query of the table view using hooks



Sometimes, you need to change the data format of one or more fields in the table view. To do so, you need to edit the SQL query used to display the table view. However, you won't find this query if you search your AppGini application code! Instead, this query is constructed from an associative array coded into the generated "tablename_view.php" file (where tablename is the name of the concerned table).

For example, if we open the file "customers_view.php" from the Northwind demo, we can see the array for constructing the table view query as follows (code below is as generated by AppGini 5.12):
	// Fields that can be displayed in the table view
	$x->QueryFieldsTV=array(   
		"`customers`.`CustomerID`" => "CustomerID",
		"`customers`.`CompanyName`" => "CompanyName",
		"`customers`.`ContactName`" => "ContactName",
		"`customers`.`ContactTitle`" => "ContactTitle",
		"`customers`.`Address`" => "Address",
		"`customers`.`City`" => "City",
		"`customers`.`Region`" => "Region",
		"`customers`.`PostalCode`" => "PostalCode",
		"`customers`.`Country`" => "Country",
		"`customers`.`Phone`" => "Phone",
		"`customers`.`Fax`" => "Fax"
	);

So, if we need to change the format of the phone field, for example, we can edit `customers`.`Phone` at line 12 in the above code. However, if we use AppGini later to regenerate our application, changes would be overwritten and lost. So, we should use the tablename_init() hook instead to avoid loosing our customized changes.

So, rather than editing the generated "customers_view.php" file, let's edit the "hooks/customers.php" file instead. In that file, we can find this code:
	function customers_init(&$options, $memberInfo, &$args){
	
		return TRUE;
	}

We should insert our query change(s) inside the above function block. To modify the phone field for example, we could use the following code to modify the phone field part of the associative array:
	function customers_init(&$options, $memberInfo, &$args){
		$old_options = $options->QueryFieldsTV;
		foreach($old_options as $field => $caption){
			/* Keep all fields as-is except for the phone field */
			if($field == '`customers`.`Phone`'){
				/* Display only the first four digits of the phone field */
				$new_options["SUBSTRING(`customers`.`Phone`, 1, 4)"] = $caption;
			}else{
				/* Keep all other fields as-is */
				$new_options[$field] = $caption;
			}
			
			$options->QueryFieldsTV = $new_options;
		}

		return TRUE;
	}

Line 7 in the above code is where we modify the key of the associative array $options->QueryFieldsTV (the key represents how the field will be formatted in the SQL query).

In the above example, we modified only one field. You can modify multiple fields by using one or more elseif blocks:
	function customers_init(&$options, $memberInfo, &$args){
		$old_options = $options->QueryFieldsTV;
		foreach($old_options as $field => $caption){
			/* Keep all fields as-is except for the phone field */
			if($field == '`customers`.`Phone`'){
				/* Display only the first four digits of the phone field */
				$new_options["SUBSTRING(`customers`.`Phone`, 1, 4)"] = $caption;
			elseif($field == '`customers`.`CustomerID`'){
				/* Modify the CustomerID field here same as above */
			}else{
				/* Keep all other fields as-is */
				$new_options[$field] = $caption;
			}
			
			$options->QueryFieldsTV = $new_options;
		}

		return TRUE;
	}

Now, you have full control over the table view query ... Have fun!