dimanche 29 mars 2015

JSON Object building in SQL. Should I build the objects in SQL or in my Application?

I am generating JSON objects via a SQL query, however, I am thinking that I should be building the objects in my application logic (node.js) instead. Given the following structure and query; what would be a way to query the data so that it could easily be transformed?


For full example @see http://ift.tt/1EhUkHU


** Expected Output JSON **



{
id: <int>
parent_comment_id: <int>
content: <string>
created: <int>
user: {
id: <int>
username: <string>
name_first: <string>
name_last: <string>
}
tags: {
<string>:<int|string>
}
}


Table Structure



CREATE TABLE `comment` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`parent_comment_id` bigint(20) unsigned DEFAULT NULL,
`user_id` bigint(20) unsigned NOT NULL,
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
;
CREATE TABLE `comment_tag` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL,
`ref_type` varchar(64) NOT NULL,
`ref_text` varchar(255) DEFAULT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
;
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL UNIQUE KEY,
`name_first` varchar(64) NOT NULL,
`name_last` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
;


Current Query



SELECT
`c`.`id` AS `id`,
`c`.`parent_comment_id` AS `parent_comment_id`,
`c`.`content` AS `content`,
`c`.`created` AS `created`,
CONCAT('{',
CONCAT('"id": "', `u`.`id`, '",'),
CONCAT('"username": "', `u`.`username`, '",'),
CONCAT('"name_first": "', `u`.`name_first`, '",'),
CONCAT('"name_last": "', `u`.`name_last`, '"'),
'}') AS `user`,
IF(`t`.`id` IS NULL, NULL, CONCAT('{',GROUP_CONCAT(
CONCAT_WS(
':',
CONCAT('"',`t`.`ref_type`, '"'),
`t`.`ref_id`,
IF(`t`.`ref_text` IS NOT NULL, CONCAT('"',`t`.`ref_text`,'"'), NULL)
)), '}')) AS `tags`
FROM `comment` AS `c`
JOIN `user` AS `u`
ON `c`.`user_id` = `u`.`id`
LEFT JOIN `comment_tag` AS `t`
ON `c`.`id` = `t`.`comment_id`
GROUP BY `c`.`id`

Aucun commentaire:

Enregistrer un commentaire