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

-

i generating json objects via sql query, however, thinking should building objects in application logic (node.js) instead. given following structure , query; way query data transformed?

for full example @see http://sqlfiddle.com/#!9/81cf5/1

** 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` `id`,   `c`.`parent_comment_id` `parent_comment_id`,   `c`.`content` `content`,   `c`.`created` `created`,   concat('{',      concat('"id": "', `u`.`id`, '",'),     concat('"username": "', `u`.`username`, '",'),     concat('"name_first": "', `u`.`name_first`, '",'),     concat('"name_last": "', `u`.`name_last`, '"'),   '}') `user`,   if(`t`.`id` null, null, concat('{',group_concat(      concat_ws(       ':',       concat('"',`t`.`ref_type`, '"'),       `t`.`ref_id`,       if(`t`.`ref_text` not null, concat('"',`t`.`ref_text`,'"'), null)     )), '}')) `tags` `comment` `c` join `user` `u` on `c`.`user_id` = `u`.`id` left join `comment_tag` `t` on `c`.`id` = `t`.`comment_id` group `c`.`id`


Comments

Post a Comment

Popular posts from this blog

google chrome - Developer tools - How to inspect the elements which are added momentarily (by JQuery)? -

-
i looking @ how website designed using inspector in chrome. can use firefox developer tools or firefox web developer add-on or chrome web developer extension. so website has slide-show , using jquery it. looking @ markup , css in inspector, i see 2 things constantly changing slides change: one opacity css property understandable, i'd see changing values, changes fast see read anything. second- a div added in div slides change momentarily , moment when appears short notice except div . so question is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? where can inspect div element gets added such short moment , , gets removed? , can see changes in value of opacity takes place? is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? if opacity animated css transition or animation,
Read more

angularjs - Showing an empty as first option in select tag -

-
my app showing empty select field first entry. have read other solution given , tried still not working. my controller code is: .controller('usercreatecontroller', function(user,profile,auth) { alert("hello"); var vm = this; vm.type = 'create'; profile.all() //this service fetch profiles shown in select field .success(function(data){ vm.profile = data; }); vm.userdata.profile = vm.profile[0]; //here have set default value select field and html page is: <label class="col-sm-2 control-label">profile</label> <div class="col-sm-8"> <select ng-model="user.userdata.profile" ng-options="person._id person.profilename person in user.profile"> </select> </div> hierarchy of vm.profile { "_id": 46, "profile": objectid("5516498e95b84548156db754"), "isactive": true, "password": "$2
Read more

php - Cloud9 cloud IDE and CakePHP -

-
i attempting cakephp running on cloud9, cloud based ide. unaware, cloud collaboration ide intermixes services such bitbucket. site gives sudo rights, , installed cakephp using these instructions: https://www.digitalocean.com/community/tutorials/how-to-install-cakephp-on-an-ubuntu-12-04-vps when attempt run database exported machine cakephp installed, error message "url rewriting not configured on server." attempted solve issue using official guide cakephp: http://book.cakephp.org/2.0/en/installation/url-rewriting.html but no cigar, same error message. has gotten cakephp working, or aware of cloud ide free/low cost cloud9 support cakephp? here's how got working now: sudo apt-get update sudo apt-get install php5-intl curl -ss https://getcomposer.org/installer | php -- --filename=composer ./composer create-project --prefer-dist cakephp/app this creates project in /app default. move workspace docroot: mv app/* ./ mv app/.* ./ rm -rf app then de
Read more