I have the requirement to build a dynamically assigned data attribute driven storage and retrieval structure. I want to handle as much of it as possible on the MySQL side and less on the application side.
I have multiple data profiles that have an arbitrary number of attributes assigned to the profile. Example being profile A has 20 entity_keys assigned to it who's value may be any one of 4 data storage types. The data points are assigned a name and a storage type and the associated value.
What I would like to do is a data driven join similar to
http://www.artfulsoftware.com/infotree/queries.php#449
which I have been able to do successfully mapping the the class_id from the profile_map, Where I'm stuck is out to join the other two table's into the result.
The data_profile stores the ID's for the data models that can have intersecting entity_keys assigned to them. The data_profile_map holds the corresponding data maps to there storage table and there key names in the entity_keys in total join up to 7 tables of data together.
I would like to select the data_profile.data_id from the data_profile joining the data_profile_map on the data_id, join the entity_key_id to the entity_keys table to get the entity_value (the name of the value) and the corresponding value data from there storage tables meta_*
I have a functional query that works from the data_profile_map conditionally using the CASE method in
http://www.artfulsoftware.com/infotree/queries.php#449 but it is returning BLOB data back on the values. I also can't seem to retrieve the entity_value or do this from the main data_profile table.
I've done this in the past but it was done mostly on the application side performing multiple single meta_value joins between the 4 tables, I'm hoping someone here has the wisdom to do this through a single query
working query of just data_profile_map to the meta tables that is conditional on the type_id, the type ID was in the entity_keys table but I as not able to successfully join them between the 3 tables
Select
data_profile_map.data_id,
data_profile_map.entity_type_id
CASE data_profile_map.type_id
WHEN 'meta_char' THEN m1.value
WHEN 'meta_int' THEN m2.value
WHEN 'meta_text' THEN m3.value
WHEN 'meta_varchar' THEN m4.value
WHEN 'meta_date' THEN m5.value
ELSE 'ERROR'
end as result
FROM data_profile_map
LEFT JOIN meta_char as m1 on data_profile_map.value_id = m1.value_id
LEFT JOIN meta_int as m2 on data_profile_map.value_id = m2.value_id
LEFT JOIN meta_text as m3 on data_profile_map.value_id = m3.value_id
LEFT JOIN meta_varchar as m4 on data_profile_map.value_id = m4.value_id
LEFT JOIN meta_date as m5 on data_profile_map.value_id = m5.value_id
table structures are below:
-- Server version: 5.1.41
-- PHP Version: 5.3.2-1ubuntu4.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `datamodel`
--
-- --------------------------------------------------------
--
-- Table structure for table `data_profile`
--
CREATE TABLE IF NOT EXISTS `data_profile` (
`data_id` int(11) unsigned NOT NULL,
`class_id` tinyint(2) unsigned NOT NULL,
`entity_key_id` int(6) unsigned NOT NULL,
KEY `data_id` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `data_profile_map`
--
CREATE TABLE IF NOT EXISTS `data_profile_map` (
`data_id` int(11) unsigned NOT NULL,
`type_id` tinyint(2) unsigned NOT NULL,
`value_id` int(6) unsigned NOT NULL,
`entity_key_id` int(6) unsigned NOT NULL,
KEY `data_id` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `entity_keys`
--
CREATE TABLE IF NOT EXISTS `entity_keys` (
`key_id` int(11) NOT NULL AUTO_INCREMENT,
`entity_value` varchar(255) NOT NULL,
PRIMARY KEY (`key_id`),
UNIQUE KEY `entity_value` (`entity_value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `meta_date`
--
CREATE TABLE IF NOT EXISTS `meta_date` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` date DEFAULT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `meta_int`
--
CREATE TABLE IF NOT EXISTS `meta_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `meta_text`
--
CREATE TABLE IF NOT EXISTS `meta_text` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` text NOT NULL,
PRIMARY KEY (`value_id`),
KEY `value` (`value`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `meta_varchar`
--
CREATE TABLE IF NOT EXISTS `meta_varchar` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------