article full select query using mysql concat, group_concat as json field

article full select with foreign table by one query

SELECT a.*, mediatype.code as mediatypecode, (SELECT CONCAT('[', GROUP_CONCAT(DISTINCT(CONCAT(CONCAT( CONCAT('{"id":"', attribute.id, '"'), CONCAT(', "filterable":"', attribute.filterable, '"') ), CONCAT(', "title":"', REPLACE(attribute.title, '"', '"'), '"'), CONCAT(', "value":"', REPLACE(article_attribute.value, '"', '"'), '"}'))) ORDER BY attribute.ordering ASC SEPARATOR ', ' ), ']') as attributes FROM (article) INNER JOIN article_attribute ON article.id = article_attribute.article_id INNER JOIN attribute ON article_attribute.attribute_id = attribute.id INNER JOIN attribute_item ON attribute.id = attribute_item.attribute_id WHERE article.id = a.id) as attributes, CONCAT('[', GROUP_CONCAT(DISTINCT(CONCAT(CONCAT( CONCAT('{"id":"', media.id, '"'), CONCAT(', "typecode":"', media.typecode, '"'), CONCAT(', "extension":"', media.extension, '"') ), CONCAT(', "path":"', REPLACE(media.path, '"', '"'), '"'), CONCAT(', "filename":"', REPLACE(media.filename, '"', '"'), '"}'))) ORDER BY media.typecode, article_media.ordering ASC SEPARATOR ', ' ), ']') as medias, seourl.url FROM (`article` as a) LEFT JOIN `seourl` ON `a`.`seourl_id` = `seourl`.`id` LEFT JOIN `mediatype` ON `a`.`mediatype_id` = `mediatype`.`id` LEFT JOIN `article_attribute` ON `a`.`id` = `article_attribute`.`article_id` LEFT JOIN `attribute` ON `article_attribute`.`attribute_id` = `attribute`.`id` LEFT JOIN `article_media` ON `a`.`id` = `article_media`.`article_id` LEFT JOIN `media` ON `article_media`.`media_id` = `media`.`id` WHERE `a`.`id` = '1' AND `mediatype`.`code` = 'zar' AND (a.approved = 1) GROUP BY `a`.`id` ORDER BY `a`.`createddate` desc