PHP Versions Question posted in
You can find a list of all versions here.

Php versions – MySQL item and its attributes in a single query

I have created MySQL data model to store content along with its custom attributes/field. Below is a simplified version of it.

One for storing content:

CREATE TABLE `cms_content` (
  `id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `content_type_id` int(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `cms_content`
  ADD PRIMARY KEY (`id`),
  ADD KEY `content_type_id` (`content_type_id`);

The second one for content type (e.g. article, movie, person):

CREATE TABLE `cms_content_type` (
  `id` int(6) NOT NULL,
  `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `cms_content_type`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `key` (`name`);

Custom fields:

CREATE TABLE `cms_custom_field` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `content_type_id` int(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `cms_custom_field`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`,`content_type_id`),
  ADD KEY `name_2` (`name`);

And finally one for connecting custom fields and the data:

CREATE TABLE `cms_content_data` (
  `id` int(11) NOT NULL,
  `custom_field_id` int(11) NOT NULL,
  `content_id` int(11) NOT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value_content_id` int(11) DEFAULT NULL,
  `value_taxonomy_value_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `cms_content_data`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `key` (`value`,`content_id`,`custom_field_id`) USING BTREE,
  ADD KEY `content_id` (`content_id`),
  ADD KEY `custom_field_id` (`custom_field_id`),
  ADD KEY `value` (`value`),
  ADD KEY `value_content_id` (`value_content_id`),
  ADD KEY `value_taxonomy_value_id` (`value_taxonomy_value_id`),
  ADD KEY `custom_field_id_2` (`custom_field_id`,`content_id`);

The challenge

I need to create a query that fetched content of a certain type with all of its custom fields.
NB! One trick here is that there can be multiple content_data values for one custom field.
E.g. a movie has a field genre which can have multiple rows in content_data.

The non-working query

I was able to dynamically generate the following query:

SELECT 
`content`.`id` AS `_content_id`, 
`content`.`title` AS `_content_title`, 
`content_data_0`.`value_taxonomy_value_id` AS `genres`, 
`content_data_1`.`value_content_id` AS `production_company`, 
`content_data_2`.`value` AS `date_released`, 
`content_data_3`.`value` AS `runtime`, 
`content_data_4`.`value` AS `tagline`, 
`content_data_5`.`value` AS `imdb_rating`, 
`content_data_6`.`value` AS `rt_rating`, 
`content_data_7`.`value` AS `imdb_id`, 
`content_data_8`.`value` AS `trailer_url`, 
`content_data_9`.`value` AS `api_id`, 
`content_data_10`.`value_taxonomy_value_id` AS `rating`, 
`content_data_11`.`value` AS `revenue`, 
`content_data_12`.`value` AS `poster_original_path`, 
`content_data_13`.`value` AS `rt_rating_users`, 
`content_data_14`.`value` AS `rt_url`, 
`content_data_15`.`value` AS `rt_consensus`, 
`content_data_16`.`value` AS `mc_rating`, 
`content_data_17`.`value` AS `year`, 
`content_data_18`.`value` AS `budget`, 
`content_data_19`.`value_taxonomy_value_id` AS `original_language`, 
`content_data_20`.`value_taxonomy_value_id` AS `production_countries`, 
`content_data_21`.`value` AS `original_title` 
FROM `cms_content` AS `content` 
INNER JOIN cms_content_type ON cms_content_type.id = content.content_type_id
INNER JOIN `cms_content_data` AS `content_data_0` ON `content_data_0`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_1` ON `content_data_1`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_2` ON `content_data_2`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_3` ON `content_data_3`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_4` ON `content_data_4`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_5` ON `content_data_5`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_6` ON `content_data_6`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_7` ON `content_data_7`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_8` ON `content_data_8`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_9` ON `content_data_9`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_10` ON `content_data_10`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_11` ON `content_data_11`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_12` ON `content_data_12`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_13` ON `content_data_13`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_14` ON `content_data_14`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_15` ON `content_data_15`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_16` ON `content_data_16`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_17` ON `content_data_17`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_18` ON `content_data_18`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_19` ON `content_data_19`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_20` ON `content_data_20`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_data` AS `content_data_21` ON `content_data_21`.`content_id` = `content`.`id` 
INNER JOIN `cms_content_type` AS `content_type` ON `content_type`.`id` = `content`.`content_type_id` 

WHERE `content_type`.`name` = 'movie' 
AND `content_data_0`.`custom_field_id` = 141 
AND `content_data_1`.`custom_field_id` = 143 
AND `content_data_2`.`custom_field_id` = 144 
AND `content_data_3`.`custom_field_id` = 146
AND `content_data_4`.`custom_field_id` = 148 
AND `content_data_5`.`custom_field_id` = 154 
AND `content_data_6`.`custom_field_id` = 155 
AND `content_data_7`.`custom_field_id` = 156 
AND `content_data_8`.`custom_field_id` = 158 
AND `content_data_9`.`custom_field_id` = 159 
AND `content_data_10`.`custom_field_id` = 162 
AND `content_data_11`.`custom_field_id` = 163 
AND `content_data_12`.`custom_field_id` = 164 
AND `content_data_13`.`custom_field_id` = 174 
AND `content_data_14`.`custom_field_id` = 175 
AND `content_data_15`.`custom_field_id` = 176 
AND `content_data_16`.`custom_field_id` = 177 
AND `content_data_17`.`custom_field_id` = 185 
AND `content_data_18`.`custom_field_id` = 184 
AND `content_data_19`.`custom_field_id` = 186 
AND `content_data_20`.`custom_field_id` = 187 
AND `content_data_21`.`custom_field_id` = 188 
DESC LIMIT 100

The problems here:
1. Wrong return. Apparently, since there can be multiple values for every content_data MySQL combines them in all sorts of way and I end up with one movie with various combinations of custom fields.
2. The query is super slow (probably because of many joins as well as combinations of field values)

The question

Is there any way in MySQL (plus some PHP backend magic, of course) to return all the required data in one single query? Considering the current data model.

As for multiple rows, having values in comma-separated field value would work for me (unless there is a better way).

Or is this data model simply very flawed and I need to change it somehow?

Thanks in advance!

Tags:

2

Answers


  1. 0

    You want conditional aggregation. I think this is:

    SELECT c.id as `_content_id`, 
       c.title AS `_content_title`,
FROM content c LEFT JOIN
     (SELECT ct.content_id,
             MAX(CASE WHEN ct.custom_field_id = 141 THEN ct.value_taxonomy_value_id END as genres,
             MAX(CASE WHEN ct.custom_field_id = 143 THEN ct.value_taxonomy_value_id END as production_company,
             MAX(CASE WHEN ct.custom_field_id = 144 THEN ct.value END as date_released,
             . . .
      FROM cms_content_type ct
      GROUP BY ct.content_id
     ) ct
     ON ct.content_id = c.contentId;

    Note: It seems strange that value is not being used for all the columns. You might want GROUP_CONCAT() is there are multiple rows for a given content_id with the same customer_field_id.

    Login or Signup to reply.
  2. 0

    You could approach this with conditional aggregation. With this technique, a single join is need, and then conditional aggregate expressions pull out the expected values.

    For your quer, that would look like:

    SELECT 
    c.id content_id
    c.title content_title,
    MAX(CASE WHEN d.custom_field_id = 141 THEN d.value END) genres,
    MAX(CASE WHEN d.custom_field_id = 143 THEN d.value END) production_company,
    MAX(CASE WHEN d.custom_field_id = 144 THEN d.value END) date_released
    ...
FROM cms_content c
INNER JOIN cms_content_type t ON t.id = c.content_type_id
INNER JOIN cms_content_data d ON d.content_id = c.id 
WHERE t.name = 'movie' 
GROUP BY c.id, c.title
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search