I’m thinking supporting a virtual fields might be a good time to start looking into views. However, before we get to that let me discuss some of what I’m doing.
I have various tables such; terms, nodes, users, links – all your usual content management suspect entities. What I am in the process of doing is making it possible for the definitions of each of those items and others to be extended through the GUI.
At this point in time what I have is two tables. One table has all the information about the field, including a foreign key reference to the table it belongs. The other table stores the value for the entities virtual field.
Fields Table
CREATE TABLE `MCP_fIELDS` (
`fields_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sites_id` bigint(20) unsigned NOT NULL,
`creators_id` bigint(20) unsigned NOT NULL COMMENT 'person who created field',
`entity_type` varchar(48) NOT NULL COMMENT 'Entity type such as; node type, user, etc',
`entities_id` bigint(20) unsigned DEFAULT NULL COMMENT 'optional ID of entity row such as; specified node type for node type extensions',
`cfg_name` varchar(128) NOT NULL COMMENT 'Unique dynamic field internal reference name',
`cfg_label` varchar(128) NOT NULL COMMENT 'Label that will be shown next to form input',
`cfg_description` text COMMENT 'Description shown to user about form purpose, contents, etc',
`cfg_required` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'whether the field is required ie. not allowed to be empty',
`cfg_default` text COMMENT 'Default value for the field',
`cfg_min` smallint(5) unsigned DEFAULT NULL COMMENT 'minimum number of characters, if applicable',
`cfg_max` smallint(5) unsigned DEFAULT NULL COMMENT 'maximum number of characters, if applicable',
`cfg_type` varchar(128) DEFAULT NULL COMMENT 'application field type',
`cfg_values` longtext COMMENT 'serialized array of select values - when supplied field is represented as select menu',
`cfg_sql` text COMMENT 'Custom SQL to derive values to shown to user to select - use with caution',
`cfg_dao_pkg` varchar(255) DEFAULT NULL COMMENT 'DAO package binding to derive select values',
`cfg_dao_method` varchar(128) DEFAULT NULL COMMENT 'DAO package binding method to call',
`cfg_dao_args` longtext COMMENT 'Serialized array of arguments to pass to DAO binding method call',
`cfg_textarea` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the field will be displayed as a textarea',
`cfg_static` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'make this field invisible to user interface, always use default value',
`cfg_size` tinyint(3) unsigned DEFAULT NULL COMMENT 'size attribute of a select menu',
`cfg_serialized` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether data will be stored serialized',
`db_value` enum('varchar','text','int','price','bool') NOT NULL DEFAULT 'text' COMMENT 'database value mapping storage column',
`db_ref_table` varchar(128) DEFAULT NULL COMMENT 'Foreign key reference table',
`db_ref_col` varchar(128) DEFAULT NULL COMMENT 'Foreign key reference table column',
PRIMARY KEY (`fields_id`),
UNIQUE KEY `sites_id` (`sites_id`,`entity_type`,`entities_id`,`cfg_name`),
KEY `entity_type` (`entity_type`,`entities_id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
Field Values Table
CREATE TABLE `MCP_fIELD_VALUES` (
`field_values_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fields_id` bigint(20) unsigned NOT NULL COMMENT 'Field foreign key',
`rows_id` bigint(20) unsigned NOT NULL COMMENT 'Field Entity row id',
`db_varchar` varchar(255) DEFAULT NULL COMMENT 'Variable length string under 256 characters',
`db_text` longtext COMMENT 'Text entry longer than 255 characters',
`db_int` bigint(20) DEFAULT NULL COMMENT 'Neg/Pos integer value',
`db_price` decimal(20,2) DEFAULT NULL COMMENT 'Price value',
`db_bool` tinyint(3) unsigned DEFAULT NULL COMMENT 'Boolean binary 0 or 1 representation',
PRIMARY KEY (`field_values_id`),
UNIQUE KEY `fields_id` (`fields_id`,`rows_id`),
KEY `db_varchar` (`db_varchar`),
KEY `db_int` (`db_int`),
KEY `db_price` (`db_price`),
KEY `db_bool` (`db_bool`),
FULLTEXT KEY `db_text` (`db_text`),
FULLTEXT KEY `db_varchar_2` (`db_varchar`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
I’m trying to keep this entire functionality as separate from the core, as possible. Right now, these virtual fields just get added to query results. They can’t actually be referenced in a query. So for every result row the below query is called to grab all a rows virtual field and values.
/*
* fetch values for fields
*
* Intention is to keep this light considering the amount of times
* it may run per a request. All available table indexes are used
* to increase effeciency.
*/
$strSQL = sprintf(
"SELECT
f.cfg_name field_name
,CASE
WHEN fv.fields_id IS NULL AND f.cfg_default IS NOT NULL
THEN f.cfg_default
WHEN fv.fields_id IS NOT NULL AND f.db_value = 'varchar'
THEN fv.db_varchar
WHEN fv.fields_id IS NOT NULL AND f.db_value = 'bool'
THEN fv.db_bool
WHEN fv.fields_id IS NOT NULL AND f.db_value = 'int'
THEN fv.db_int
WHEN fv.fields_id IS NOT NULL AND f.db_value = 'price'
THEN fv.db_price
WHEN fv.fields_id IS NOT NULL AND f.db_value = 'text'
THEN fv.db_text
ELSE NULL END field_value
FROM
MCP_FIELDS f
LEFT OUTER
JOIN
MCP_FIELD_VALUES fv
ON
fv.fields_id = f.fields_id
AND
fv.rows_id = %s
WHERE
f.sites_id = %s
AND
f.entity_type = '%s'
AND
f.entities_id %s"
,$this->_objMCP->escapeString($intRowsId)
,$this->_objMCP->escapeString($intSitesId !== null?$intSitesId:$this->_objMCP->getSitesId())
,$this->_objMCP->escapeString($strEntityType)
,$intEntitiesId !== null?"= {$this->_objMCP->escapeString($intEntitiesId)}":' IS NULL'
);
Which works well, but does not allow referencing the fields in queries. To reference the fields in queries 2 joins would need to be added for each field referenced, which will become an efficiency nightmare.
Instead, I was wondering if there was a way a View could be generated that gets automatically updated. What I’m essentially after is a “virtual table” that is derived from the virtual fields for a concrete table. So in the end there might be the below views:
MCP_FIELDS_USERS
MCP_FIELDS_NODE_TYPES_4
MCP_FIELDS_NODE_TYPES_34
MCP_FIELDS_NODE_TYPES_201
MCP_FIELDS_VOCABULARY_89
Essentially, those would be the virtual tables built from the field data. Than all I would need to do is to join on the single table to add all virtual fields, rather than x2 for each virtual field that exists. Plus, the relationship between a concrete row and virtual would be 1:1 so filtering, sorting, grouping etc would be much more efficient. Indexes could even be added on a case by case basis to the virtual fields tables.
Here is an example of how the fields are added at this point:
$strSQL = sprintf(
'SELECT
SQL_CALC_FOUND_ROWS %s
,n.nodes_id tmp_nodes_id
,n.node_types_id tmp_node_types_id
FROM
MCP_NODES n
INNER
JOIN
MCP_USERS u
ON
n.authors_id = u.users_id
INNER
JOIN
MCP_NODE_TYPES t
ON
n.node_types_id = t.node_types_id
%s %s %s'
,$strSelect
,$strWhere === null?'':" WHERE $strWhere"
,$strSort === null?'':" ORDER BY $strSort"
,$strLimit === null?'':"LIMIT $strLimit"
);
$arrNodes = $this->_objMCP->query($strSQL,$arrBound);
/*
* Add in dynamic fields - Internal columns used to add dynamic field data after removed
*/
foreach($arrNodes as &$arrNode) {
$arrNode = $this->_objMCP->addFields($arrNode,$arrNode['tmp_nodes_id'],'MCP_NODE_TYPES',$arrNode['tmp_node_types_id']);
unset($arrNode['tmp_nodes_id'],$arrNode['tmp_node_types_id']);
}
The query is ran to collect the concrete data and all the fields are added after, one by one. Each call to MCP::addFields() runs the previously mentioned query. Instead I would just like to be able to join on a single virtual table and grab them.
I guess what I’m asking, is something like this possible?
Apologize for mixing the PHP w/ MySQL but the issue really requires an understanding of both though I think the answer is going to reside on the database side.
Thanks