Razuana serach api-using sortby=sizeasc and sizedesc


#1

Razuana serach api-using sortby=sizeasc and sizedesc .what does it mean
API:http://172.30.65.222:8080/razuna/global/api2/search.cfc?method=searchassets&folderid=B0C4F900E6AD4DCE9EED0169A4F0319D&searchfor=*&api_key=XXXXXXX&sortby=sizeasc &__BDQUERYFORMA

Response: {“message”:“General SQL Error”,“errorcode”:“errorCode.sqlError”,“detail”:“Database reported: Order by expression “SIZEASC” must be in the result list in this case; SQL statement:\nSELECT /* 28C2DC3ADAC34BA5953FB2B2FE7105F3search_images_api / cast(ifnull(i.img_id, 0) AS char) id, \r\n\t\t\ti.img_filename filename, \r\n\t\t\ti.folder_id_r folder_id, \r\n\t\t\tfo.folder_name,\r\n\t\t\ti.img_extension extension, \r\n\t\t\t’dummy’ as video_image,\r\n\t\t\ti.img_filename_org filename_org, \r\n\t\t\t’img’ as kind, \r\n\t\t\ti.thumb_extension extension_thumb, \r\n\t\t\ti.path_to_asset, \r\n\t\t\ti.cloud_url, \r\n\t\t\ti.cloud_url_org,\r\n\t\t\tcast(ifnull(i.img_size, 0) AS char) AS size, cast(i.img_size as decimal(12,0)) AS size_num,\r\n\t\t\ti.img_width AS width,\r\n\t\t\ti.img_height AS height,\r\n\t\t\t’0’ AS isalias,\r\n\t\t\tit.img_description description, \r\n\t\t\tit.img_keywords keywords,\r\n\t\t\ti.img_create_time dateadd,\r\n\t\t\ti.img_change_time datechange,\r\n\t\t\tCASE WHEN NOT (i.img_group is null OR i.img_group=’’) THEN (SELECT expiry_date FROM raz1_images WHERE img_id=i.img_group) ELSE i.expiry_date END expiry_date_actual,\r\n\t \r\n\t\t\t concat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,i.img_filename_org) AS local_url_org,\r\n\t\t\t CASE WHEN (i.img_group is null OR i.img_group=’’)\r\n\t\t\t \tTHEN\r\n\t\t\t\t\t\tconcat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,‘thumb_’,i.img_id,’.’,i.thumb_extension)\r\n\t\t\t\t\tELSE\r\n\t\t\t\t\t\tconcat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,‘thumb_’,i.img_group,’.’,i.thumb_extension)\r\n\t\t\t\tEND as local_url_thumb,\r\n\t \r\n\t \t\r\n\t \t\t(\r\n\t \t\t\tSELECT GROUP_CONCAT(DISTINCT ic.col_id_r ORDER BY ic.col_id_r SEPARATOR ‘,’) AS col_id\r\n\t \t\t\tFROM raz1_collections_ct_files ic\r\n\t \t\t\tWHERE ic.file_id_r = i.img_id\r\n\t \t\t\tAND ic.in_trash = ?\r\n\t \t\t) AS colid\r\n\t \t\r\n\t \t,\r\n\t\t\tx.colorspace,\r\n\t\t\tx.xres AS xdpi,\r\n\t\t\tx.yres AS ydpi,\r\n\t\t\tx.resunit AS unit,\r\n\t\t\ti.hashtag AS md5hash,\r\n\t\t\ti.img_filename filename_forsort,\r\n\t\t\t’13’ as cnt,\r\n\t\t\ti.img_create_time date_create,\r\n\t\t\ti.img_change_time date_change,\r\n\t\t\tCASE \r\n\t\t\t\tWHEN (i.img_group is null OR i.img_group=’’) THEN ‘original’\r\n\t\t\t\tELSE ‘rendition’\r\n\t\t\tEND as file_type\r\n\t\t\t\r\n\t\t\t\r\n\t\t\tFROM raz1_images i \r\n\t\t\tLEFT JOIN raz1_images_text it ON i.img_id = it.img_id_r AND it.lang_id_r = 1\r\n\t\t\tLEFT JOIN raz1_xmp x ON x.id_r = i.img_id\r\n\t\t\tLEFT JOIN raz1_folders fo ON fo.folder_id = i.folder_id_r AND fo.host_id = i.host_id\r\n\t\t\tWHERE i.host_id = ?\r\n\t\t\t\r\n\t\t\t\tAND\ti.img_id IN (‘0’,‘11BDEF0B3A8D434F8173DBAC4C5B8695’,‘17794686D48B47B289D435DFA28E5040’,‘18DF04872B65480C83C54D7318D80F3D’,‘715A22F038CB41F399CEE945387EA3CB’,‘83079F3554074DD3A275C4055E78E568’,‘85B055710CCF4BC68BEBC76D2596A7E0’,‘A5654DCB97CB4C92AA0F5FDC608405BA’,‘AF14E38A5C714819AC1ECC5B18573DE9’,‘E2585C5A48404FE3A008BA53BFF6BC6F’,‘EBD45B24B3604E37AF5AF9F1851131B7’,‘F82C4002A621467C8539D780B4BD18A7’,‘FB295F8196A14DF2A252E26CB63E9B3A’,‘FEE8F24D1B914734A929B6874C674697’)\r\n\t\t\t\r\n\t\t\tAND i.in_trash = ?\r\n\t\t\t\r\n\t\t\tAND CASE\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS (SELECT 1 FROM ct_groups_users WHERE ct_g_u_user_id =‘3EB1BDA6-ADC2-4198-8F5F4DCC12E02462’ and ct_g_u_grp_id in (‘1’,‘2’)) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS(\r\n\t\t\t\t\tSELECT fg.folder_id_r\r\n\t\t\t\t\tFROM raz1_folders_groups fg\r\n\t\t\t\t\tWHERE fg.host_id = ?\r\n\t\t\t\t\tAND fg.folder_id_r = i.folder_id_r\r\n\t\t\t\t\tAND fg.grp_permission IN (?,?,?)\r\n\t\t\t\t\tAND fg.grp_id_r IN (?)\r\n\t\t\t\t\t) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS(\r\n\t\t\t\t\tSELECT fg2.folder_id_r\r\n\t\t\t\t\tFROM raz1_folders_groups fg2\r\n\t\t\t\t\tWHERE fg2.grp_id_r = ‘0’\r\n\t\t\t\t\tAND fg2.folder_id_r = i.folder_id_r\r\n\t\t\t\t\tAND fg2.host_id = ?\r\n\t\t\t\t\tAND fg2.grp_permission IN (?,?,?)\r\n\t\t\t\t\t) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN fo.folder_owner = ‘3EB1BDA6-ADC2-4198-8F5F4DCC12E02462’ THEN ‘unlocked’\r\n\t\t\t\tELSE ‘locked’ \r\n\t\t\t END = ‘unlocked’\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\tAND i.folder_id_r IN (?)\r\n\t\t\t\r\n\t\t\tGROUP BY i.img_id, i.img_filename, i.folder_id_r, fo.folder_name, i.img_extension, i.img_filename_org, i.thumb_extension, i.path_to_asset, i.cloud_url, i.cloud_url_org, i.img_size, i.img_width, i.img_height, i.img_create_time, i.img_change_time, it.img_description, it.img_keywords, x.colorspace, x.xres, x.yres, x.resunit, i.hashtag, fo.folder_name, i.img_filename, i.img_group, i.expiry_date\r\n\t\t\t\r\n\t\t\tUNION ALL\r\n\t\t\t\r\n\t\t\tSELECT / 28C2DC3ADAC34BA5953FB2B2FE7105F3search_images_api */ cast(ifnull(i.img_id, 0) AS char) id, \r\n\t\t\ti.img_filename filename, \r\n\t\t\tct.folder_id_r folder_id, \r\n\t\t\tfo.folder_name,\r\n\t\t\ti.img_extension extension, \r\n\t\t\t’dummy’ as video_image,\r\n\t\t\ti.img_filename_org filename_org, \r\n\t\t\t’img’ as kind, \r\n\t\t\ti.thumb_extension extension_thumb, \r\n\t\t\ti.path_to_asset, \r\n\t\t\ti.cloud_url, \r\n\t\t\ti.cloud_url_org,\r\n\t\t\tcast(ifnull(i.img_size, 0) AS char) AS size, cast(i.img_size as decimal(12,0)) AS size_num,\r\n\t\t\ti.img_width AS width,\r\n\t\t\ti.img_height AS height,\r\n\t\t\t’1’ AS isalias,\r\n\t\t\tit.img_description description, \r\n\t\t\tit.img_keywords keywords,\r\n\t\t\ti.img_create_time dateadd,\r\n\t\t\ti.img_change_time datechange,\r\n\t\t\tCASE WHEN NOT (i.img_group is null OR i.img_group=’’) THEN (SELECT expiry_date FROM raz1_images WHERE img_id=i.img_group) ELSE i.expiry_date END expiry_date_actual,\r\n\t \r\n\t\t\t concat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,i.img_filename_org) AS local_url_org,\r\n\t\t\t CASE WHEN (i.img_group is null OR i.img_group=’’)\r\n\t\t\t \tTHEN\r\n\t\t\t\t\t\tconcat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,‘thumb_’,i.img_id,’.’,i.thumb_extension)\r\n\t\t\t\t\tELSE\r\n\t\t\t\t\t\tconcat(‘http://localhost:8080//razuna/assets/1/’,i.path_to_asset,’/’,‘thumb_’,i.img_group,’.’,i.thumb_extension)\r\n\t\t\t\tEND as local_url_thumb,\r\n\t \r\n\t \t\r\n\t \t\t(\r\n\t \t\t\tSELECT GROUP_CONCAT(DISTINCT ic.col_id_r ORDER BY ic.col_id_r SEPARATOR ‘,’) AS col_id\r\n\t \t\t\tFROM raz1_collections_ct_files ic\r\n\t \t\t\tWHERE ic.file_id_r = i.img_id\r\n\t \t\t\tAND ic.in_trash = ?\r\n\t \t\t) AS colid\r\n\t \t\r\n\t \t,\r\n\t\t\tx.colorspace,\r\n\t\t\tx.xres AS xdpi,\r\n\t\t\tx.yres AS ydpi,\r\n\t\t\tx.resunit AS unit,\r\n\t\t\ti.hashtag AS md5hash,\r\n\t\t\ti.img_filename filename_forsort,\r\n\t\t\t’13’ as cnt,\r\n\t\t\ti.img_create_time date_create,\r\n\t\t\ti.img_change_time date_change,\r\n\t\t\t’original’ AS file_type\r\n\t\t\t\r\n\t\t\t\r\n\t\t\tFROM raz1_images i \r\n\t\t\tINNER JOIN ct_aliases ct ON i.img_id = ct.asset_id_r\r\n\t\t\tLEFT JOIN raz1_images_text it ON i.img_id = it.img_id_r AND it.lang_id_r = 1\r\n\t\t\tLEFT JOIN raz1_xmp x ON x.id_r = i.img_id\r\n\t\t\tLEFT JOIN raz1_folders fo ON fo.folder_id = ct.folder_id_r AND fo.host_id = i.host_id\r\n\t\t\tWHERE i.host_id = ?\r\n\t\t\t\r\n\t\t\t\tAND\ti.img_id IN (‘0’,‘11BDEF0B3A8D434F8173DBAC4C5B8695’,‘17794686D48B47B289D435DFA28E5040’,‘18DF04872B65480C83C54D7318D80F3D’,‘715A22F038CB41F399CEE945387EA3CB’,‘83079F3554074DD3A275C4055E78E568’,‘85B055710CCF4BC68BEBC76D2596A7E0’,‘A5654DCB97CB4C92AA0F5FDC608405BA’,‘AF14E38A5C714819AC1ECC5B18573DE9’,‘E2585C5A48404FE3A008BA53BFF6BC6F’,‘EBD45B24B3604E37AF5AF9F1851131B7’,‘F82C4002A621467C8539D780B4BD18A7’,‘FB295F8196A14DF2A252E26CB63E9B3A’,‘FEE8F24D1B914734A929B6874C674697’)\r\n\t\t\t\r\n\t\t\tAND (i.img_group IS NULL OR i.img_group = ‘’)\r\n\t\t\tAND i.in_trash = ?\r\n\t\t\t\r\n\t\t\tAND CASE\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS (SELECT 1 FROM ct_groups_users WHERE ct_g_u_user_id =‘3EB1BDA6-ADC2-4198-8F5F4DCC12E02462’ and ct_g_u_grp_id in (‘1’,‘2’)) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS(\r\n\t\t\t\t\tSELECT fg.folder_id_r\r\n\t\t\t\t\tFROM raz1_folders_groups fg\r\n\t\t\t\t\tWHERE fg.host_id = ?\r\n\t\t\t\t\tAND fg.folder_id_r = ct.folder_id_r\r\n\t\t\t\t\tAND fg.grp_permission IN (?,?,?)\r\n\t\t\t\t\tAND fg.grp_id_r IN (?)\r\n\t\t\t\t\t) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN EXISTS(\r\n\t\t\t\t\tSELECT fg2.folder_id_r\r\n\t\t\t\t\tFROM raz1_folders_groups fg2\r\n\t\t\t\t\tWHERE fg2.grp_id_r = ‘0’\r\n\t\t\t\t\tAND fg2.folder_id_r = ct.folder_id_r\r\n\t\t\t\t\tAND fg2.host_id = ?\r\n\t\t\t\t\tAND fg2.grp_permission IN (?,?,?)\r\n\t\t\t\t\t) THEN ‘unlocked’\r\n\t\t\t\t\r\n\t\t\t\tWHEN fo.folder_owner = ‘3EB1BDA6-ADC2-4198-8F5F4DCC12E02462’ THEN ‘unlocked’\r\n\t\t\t\tELSE ‘locked’ \r\n\t\t\t END = ‘unlocked’\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\r\n\t\t\t\tAND ct.folder_id_r IN (?)\r\n\t\t\t\r\n\t\t\tGROUP BY i.img_id, i.img_filename, ct.folder_id_r, fo.folder_name, i.img_extension, i.img_filename_org, i.thumb_extension, i.path_to_asset, i.cloud_url, i.cloud_url_org, i.img_size, i.img_width, i.img_height, i.img_create_time, i.img_change_time, it.img_description, it.img_keywords, x.colorspace, x.xres, x.yres, x.resunit, i.hashtag, fo.folder_name, i.img_filename, i.img_group, i.expiry_date\r\n\t\t\t\r\n\t\t\tORDER BY sizeasc [90068-191]”}

Neeru


#2

This error means that one of the fields is missing the “group by” clause.

Depending on the MySQL version you might need to change the “sql_mode” setting to make this work.