Table
Description
A table is a structure that allows the developer to make a query and show to the user the results. Eventually the table is going to contain links that allows the user to access to different specific features in the system linked to a specific entity shown in the table.
Information needed in order to set up a table
Get parameters
Sometimes we need to pass to the query behind the table some parameter using a get request.
Example: www.example.com/mytable?parentid=2302
In order to catch that get parameter you need to add an object in the parameters section. That object has tree properties:
- type: the type (long, string, etc..) expected the parameter to take after validation
- validation: the rules the parameter has to follow
- name: the parameter name in the URL
"parameters": [
{ "type":"long", "validation":"required|numeric", "name":"parentid" }
]If you want to know about the Validation check out the related page.
Query
We need to make a query to the datase in order to populate our table. The simplest thing to do is just to write the query in plain SQL and eventually connect the parameters needed from the GET section.
"query": {
"sql": "select id, typeid, name, description FROM mytable WHERE parentid = :parentid;",
"parameters":[
{ "type":"long", "placeholder": ":parentid", "getparameter": "parentid" }
]
}As you can see the SQL parameter is inserted in the query using a placeholder: :parametername The SQL parameter is connected to the GET parameter using: “getparameter”: “parentid” We can insert as many paremeters as we need.
If you want to know more about SQL paramenters check out the Query page.
Dummy data
Sometimes we need to design an interface not being sure yet about the shape of the database. In this case it is possible to replace the query section of the resource with a dummydata section. This will allow you to design all the interface and start to build a mockup of the application without the need to start to shape the database on the first day when ideas are still unclear.
"dummydata": [
{ "name": "Name for row 1", "description": "description for row 1" },
{ "name": "Name for row 2", "description": "description for row 2" },
{ "name": "Name for row 3", "description": "description for row 3" },
{ "name": "Name for row 4", "description": "description for row 4" },
]The dummy data will simulate a set of rows returned from the database and will populate the table structure fillng the sqlfield section in the table section of the resource.
Table Structure
The table structure is pretty easy.
We give a title to the table.
We describe a set of buttons, at the top of the table linked to specific actions. This can be useful for instance in order to link a form to create a new entity.
We describe a set of fields giving to each of them a name and a connection to the SQL query: {“headline”: “Name”, “sqlfield”: “name”}
We describe a set of actions liked to each item of the table.
"table": {
"title": "My table",
"topactions": [
{ "label": "New", "resource": "newentityform" }
]
"fields": [
{"headline": "Name", "sqlfield": "name"},
{"headline": "Description", "sqlfield": "description"}
],
"actions": [
{ "icon":"level-down", "label": "Info", "resource": "entityinfo", "parameters":[{"name": "id", "sqlfield": "id"}] },
{ "label": "Edit", "resource": "editentityform", "parameters":[{"name": "id", "sqlfield": "id"}] }
]
}Fields
The properties of the field object are:
- headline: the headline of the table column
- type: the type of the field [editable, noneditable]
- sqlfield: used in case we need to load data in a field that comes from a query
- sessionparameter: used in case we need to load data in a field that comes from a session parameter
- getparameter: used in case we need to load data in a field that comes from a getparameter parameter
- postparameter: used in case we need to load data in a field that comes from a postparameter parameter
- constant: used in case we need to load data in a field that comes from a constantparameter parameter
- composite: used in in a column we want to put more than one field
- filter: the filter functions to apply to the field.
{"headline": "Name", "composite":"${placeholder1} ${placeholder2}", "parameters": [
{ "name":"${placeholder1}", "sqlfield": "name" },
{ "name":"${placeholder2}", "sqlfield": "surname" }
]
}Value
A field can have a value property. This property allows the software developer to specify different sources for information. The sources are evaluated on by one and the first not null result becomes the value of the field. A filter can be applyed to the not-null result of a value.
{"headline": "First letter", "value": [
{ "type":"string", "sqlfield":"name", "filter":"substr,0,1" },
{ "type":"string", "constant":"" }
] }In this example this column is filled with the information stored in the “name” sql field, result of a query. If that information is not null the filter substr is applyed. In case that information is null the second element in the list is evaluated, in this case it is the constant empty string.
A complete example
Combining all the described information in a unique file we obtain something like this:
{
"name": "articlestable",
"metadata": { "type":"table", "version": "1" },
"allowedgroups": [ "author" ],
"get": {
"request": {
"parameters": []
},
"query": {
"sql": "SELECT id, title, description, tag, directory created FROM articles;",
"parameters":[]
},
"table": {
"title": "My articles",
"topactions": [
{ "label": "New", "resource": "newarticle" }
],
"fields": [
{"headline": "Title", "sqlfield": "title"},
{"headline": "Description", "sqlfield": "description"},
{"headline": "Date", "sqlfield": "created"},
{"headline": "Ctegorization", "composite":"${tag} ${directory}", "parameters": [
{ "name":"${tag}", "sqlfield": "tag" },
{ "name":"${directory}", "sqlfield": "directory" }
] },
],
"actions": [
{"label": "Edit", "resource": "editarticleform", "parameters":[{"name": "id", "sqlfield": "id"}] },
{"label": "Delete", "resource": "deletearticletransaction", "parameters":[{"name": "id", "sqlfield": "id"}] }
]
}
}
}This table script is taken from the CRUD tutorial. It is possible to download a copy of the complete example from the GitHub repository.
Dynamic table title
It is possible to have a table title built with data returned by a query.
Let’s see a complete example:
{
"name": "articlestable",
"metadata": { "type":"table", "version": "1" },
"allowedgroups": [ "author" ],
"get": {
"request": {
"parameters": []
},
"query": {
"sql": "SELECT id, title, description, tag, directory created FROM articles;",
"parameters":[]
},
"titlequery":{
"sql": "SELECT name FROM authors LIMIT 1;",
"parameters" :[]
},
"table": {
"title": { "composite":"Articles written by: '${name}'",
"parameters": [ { "name":"${name}", "sqlfield": "name" } ]
},
"topactions": [
{ "label": "New", "resource": "newarticle" }
],
"fields": [
{"headline": "Title", "sqlfield": "title"},
{"headline": "Description", "sqlfield": "description"},
{"headline": "Date", "sqlfield": "created"},
{"headline": "Ctegorization", "composite":"${tag} ${directory}", "parameters": [
{ "name":"${tag}", "sqlfield": "tag" },
{ "name":"${directory}", "sqlfield": "directory" }
] },
],
"actions": [
{"label": "Edit", "resource": "editarticleform", "parameters":[{"name": "id", "sqlfield": "id"}] },
{"label": "Delete", "resource": "deletearticletransaction", "parameters":[{"name": "id", "sqlfield": "id"}] }
]
}
}
}This table has a table query that get from database the data to show in the title.
"titlequery":{
"sql": "SELECT name FROM authors LIMIT 1;",
"parameters" :[]
}The data loaded are used in a title that is a composite structure where placeholders are replaced with data.
"title": { "composite":"Articles written by: '${name}'",
"parameters": [ { "name":"${name}", "sqlfield": "name" } ]
}