Using Custom View

WARNING! Custom View expects simple SELECT statements only, they are NOT designed to and cannot replace views provided by the database. PHPMaker supports creating database view, you should always use database views whenever available. Views provided by database allow you to use them more like regular tables.

Custom View allows you to save a simple SELECT statements in a project as a virtual table.

Converting Custom View to Database View

Custom View expects a simple SELECT statement only. If you create a Custom View with a SELECT statement with some complex clauses, or with an UNION statement instead of a single SELECT statement, the sorting or searching may fail. In such cases you should change the Custom View to database view. PHPMaker allows converting your Custom View to database view provided that the database user have CREATE VIEW privilege.

After loading the database in PHPMaker, the database objects (tables, views, custom views and reports) will be shown in the left pane (the database pane). To convert a Custom View to database view, right click the Custom View in the database pane and select Convert Custom View to View. Alternatively, you can select the Custom View first, then click Edit in the main menu and then select Convert Custom View to View. After creating the view, the existing Custom View and field settings will be moved to the new view. If your project is connected to a development database, export the new views and recreate them on your production database during deployment.

Note If you cannot change Custom View to database view and you must use Custom View, you can try to use a derived table in the format of SELECT * FROM (<Original SQL>) AS t as the SQL of the Custom View.

Creating Custom View

To create a custom view, right click the database pane and select Add Custom View. Alternatively, you can click Edit in the main menu and then select Add Custom View.

The Custom View Setup window will show up:

PHPMaker will give the new Custom View a temporary name, CustomView<n>, where n is an integer. If you want to change the name, enter a new name in the Custom View name edit box in the main toolbar.

It is recommended that the built-in visual query builder be used to build your SQL. The query builder interface is intuitive and fully supports drag-n-drop, in most cases you don't need to type the table or field names.

On the left hand side, the available tables are displayed in the table pane. On the right hand side, there are three tabs: Builder, SQL and Result. (The Result tab will only be visible after you execute the SQL.)

Builder

To build your SQL, drag your tables from the table pane on the left to the builder area and check the required fields.

To create a link between two objects (i.e. join them) you should select the field by which you want to link an object with another and drag it to the corresponding field of another object. After you finish dragging, a line will appear between the linked fields. The join type created by default is INNER JOIN, i.e. only matching records of both tables will be included in resulting dataset. To define other types of joins you should right click the link and select the Edit... item from the drop down menu or simply double-click it to open the Link Properties dialog. This dialog allows you to define join type and other link properties.

The easiest way to add a field to the list of query output fields is to check the checkbox at the left of field name in the Query Building Area. To include all the fields of an object you should click the checkbox at the left of the asterisk item of an object.

Note You should select your fields explicitly, not using the * symbol.

Another way is to select a field name from the drop-down list of the Expression column in the Columns Pane. And the most common way is to write any valid expression to the Expression column in the Columns Pane.

To remove a field from the list of query output fields you should uncheck the checkbox at the left of field name in the Query Building Area or you may remove it by unchecking the Output column checkbox. Such operations as removing lines from the Columns Pane or re-ordering output fields are available by right clicking on the left-most gray column via the drop-down menu.

To define sorting of output query fields you should use the Sort Type and Sort Order columns of the Columns Pane.

The Sort Type column allows you to specify how the fields will be sorted - in Ascending or Descending order.

The Sort Order column allows you to setup the order in which fields will be sorted, if there are more than one field to sort specified.

To cancel sorting by some field you should clear the Sort Type column for this field.

To define criteria for the expression listed in the Columns Pane you must use the Criteria column.

Here you should write the criteria omitting the expression itself. For get the following criteria in your query

WHERE (field >= 10) AND (field <= 20)

you should write

>= 10 AND <= 20

in the Criteria column.

You may specify several criteria for one expression using the Or... columns. These criteria will be concatenated in the query with the OR operator.

To setup grouping by some of the fields and/or to define aggregate functions on grouped rows you may use the Group by column.

You may select one of the following values for this column from the drop-down list:

"Expression" and "Where": These values are used when no grouping is specified. The "Expression" value is set when this expression is used as output expression in the SELECT clause and nothing else. The "Where" value is set automatically when you define a criteria to this expression that results in including this expression to the WHERE clause. Normally you shouldn't care about value of the Group By column when you don't want to define grouping.
"Group by" and "Having": These values are similar to the previous two, but used when you want to define grouping in your query. In this case you should set the "Group by" value for all columns you want to group by. Specifying criteria for the grouped columns will include these criteria in the HAVING clause. If you want to include an expression ONLY in the HAVING clause you should set the "Having" value in the Group By column for this expression.
Aggregate functions (Count, Sum, etc): By selecting one of these values you will create an aggregate expression for the value indicated in the Expression column.

SQL

You can always click the SQL tab to check the SQL generated by the query builder, the SQL editor is also syntax-highlighted to enhance the readability of the SQL.

The SQL tab is actually an SQL editor, you can type your SQL directly without using the query builder here, or paste your SQL from elsewhere, or open a saved SQL script (*.sql) from file.

Notes

If the SQL is not generated by the built-in query builder, there are chances that query builder cannot parse the SQL and display it visually in the Builder tab. However, this does not necessarily mean that the SQL is invalid, you can verify its validity by executing it (see below) and check if it returns the data you want in the Result tab. And you can create the Custom View directly by click the OK button without switching to the Builder tab.
If you type your SQL directly, always use aliases (use "AS") for fields whenever applicable. Otherwise, the alias will be assigned by the database and you will not be able to refer to the field with a known and meaningful name.

Result

When you have finished your SQL, you can test the SQL by any one of the following ways: (for "DIRECT" connection only)

Clicking the Execute button in the toolbar
Pressing F9 on your keyboard
Clicking Query in the menu and then Execute

The Result tab will become visible and display the result data. If the data is correct, the Custom View setup is almost done.

Note Executing the SQL is for testing the SQL only. The data displayed in the Result tab will not show the memo fields and blob fields, they may be represented by "(MEMO)" and "(BLOB)" only. This is not related to the data that will be displayed by the generated scripts.

When a Custom View is added, PHPMaker will load it and display it in the database pane and Table Setup page under the Custom Views node. To view the SELECT statement of the Custom View, right click the Custom View in database pane and select Object Properties. Alternatively, you can select the Custom View first, then click Project in the main menu and then select Object Properties.

To edit a custom view, right click the Custom View in the database pane and select Edit Custom View. Alternatively, you can select the Custom View first, then click Edit in the main menu and then select Edit Custom View.

When a Custom View is added or edited, PHPMaker will check the SQL. If the SQL is invalid, the icon of the Custom View will have a cross on it like , you can view the error in the Object Properties window and then edit the Custom View to correct the SQL.

Important

After a Custom View is created, it works independently from the table(s) it based on. Changing the structure of the source table(s) does not change the Custom View. If you delete a field in the source table that is used by a Custom View, the Custom View will fail. You should edit the Custom View to update the SELECT statement. Also, a Custom View has its own field settings, changing field settings in the source table does not change the field settings in related Custom Views.
During searching, sorting and grouping, PHPMaker generated code need to change the WHERE and/or ORDER BY clause of SQL for the list page dynamically. For Custom Views, since there might be fields with aliases, it is necessary to use the actual expressions of the aliases in order to ensure the result SQL works. For example, if you have a field, "UnitPrice*12 AS TotalPrice" in your SQL, you cannot sort using "ORDER BY TotalPrice", you need to use "ORDER BY UnitPrice*12". Therefore, if your Custom View contain fields with aliases, it is important that the SQL can be parsed correctly by PHPMaker. To make sure that, here are the recaps:
1. It is recommended that the built-in visual query builder be used to build your SQL.
2. You should select your fields explicitly, not using the * symbol.
3. If you type your SQL directly, always use aliases (use "AS") for fields whenever applicable.