lundi 2 février 2015

MySQL - Use group_concat function in another group_concat function


Alright i have this beautiful piece of code who help me obtain information about a fost like user name date posted comment etc, my problem is i want to extract 4 of last posts, i an obtain only one because i use a function, but with group_concat function i cand concat all 4 response in one large response converted in a json string. Here is my code:



begin
declare ipost text;

select concat('{"response":1,"posts":[',
group_concat(
'{',
'"status":"', data_comment.comment,'",',
'"date":"', data_datetime.time,'",',
'"edited":"', data_datetime.edited,'",',
'"user":"', concat(data_user_data.name,' ',data_user_data.lastname),'",',
'"photo":', '{"', group_concat(data_photo.name separator '","'),'"}'
'}'
separator ',')
,']}')
into ipost
from data_post
inner join data_user_data on data_user_data.id = data_post.id_user
inner join data_datetime on data_datetime.id = data_post.id_date
inner join data_comment on data_comment.id = data_post.id_comment
inner join fk_post__photo on fk_post__photo.id_post = data_post.id
inner join data_photo on data_photo.id = fk_post__photo.id_photo
group by data_post.id
desc
limit 1;


if(ipost is null) then
return '{"response":0}';
else
return ipost;
end if;
end


i get error: Invalid use of group function how to obtain array from this code:



'{',
'"status":"', data_comment.comment,'",',
'"date":"', data_datetime.time,'",',
'"edited":"', data_datetime.edited,'",',
'"user":"', concat(data_user_data.name,' ',data_user_data.lastname),'",',
'"photo":', '{"', group_concat(data_photo.name separator '","'),'"}'
'}'


or if is possible to obtain 4 different rows with data from this function!





Aucun commentaire:

Enregistrer un commentaire