Select Page

SELECT DISTINCT
p.ID as post_id, 'prefix_list', CONCAT('a:', COUNT(*), ':{', GROUP_CONCAT(CONCAT('i:', f.`delta`, ';s:',LENGTH(f.`field_prefix_list_value`),':"', UPPER(f.`field_prefix_list_value`), '"') SEPARATOR ';'), ';}') AS `prefixes`
FROM `Giwth4_posts` p
INNER JOIN node_field_data n ON (p.post_title = n.title COLLATE utf8mb4_unicode_ci)
INNER JOIN `node__field_prefix_list` f ON f.revision_id = n.vid
WHERE n.status = 1 AND f.deleted = 0 GROUP BY f.`entity_id` ORDER BY f.`delta`

  • It starts with a count of how many elements are in a group
  • It takes the records, grouped by the common element (in this case, entity_id)
  • It concatenates the delta that is avaialble as the array index
  • It looks at the length of the value needed
  • In my case, I needed upper case strings, so I put in an UPPER() function call
  • It separates each one with a semi-colon
  • It group concatenates that
  • It does the rest of the necessary formatting.
  • It orders the elements by their delta inside of the groups to go from lowest to highest index