How to get group by data based on customTextBlock in rest service

[harigkp]

INSERT SHORT DESCRIPTION EXPLAINING THE HIGH-LEVEL REASON FOR THE NEW ISSUE HERE.

Current behavior

Group by for customTextBlock5, I did same as category but this is not working.

Request URL:https://public-rest777.bullhornstaffing.com/rest-services/2TINES/query/JobBoardPost?where=id IN (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)&groupBy=customTextBlock5&fields=customTextBlock5,count(id)&count=500&orderBy=+customTextBlock5,-count.id&start=0
Request method:GET
Status code: 400

{
"errorMessage": "javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query",
"errorCode": 400
}

I have tried group by as following sql as well
SELECT count(jobOrderID)

-- ,[customTextBlock5]

FROM [TTTT].[dbo].[JobOrder]
where [TTTT].[dbo].[JobOrder].[jobOrderID] in (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)

group by [TTTT].[dbo].[JobOrder].[customTextBlock5]

I analyzed that customTextBlock5 data type is text in DB and you cannot make group by on text data type. I ran following and its working

SELECT count(jobOrderID)

-- ,[customTextBlock5]

FROM [BHDMQA].[dbo].[JobOrder]
where [BHDMQA].[dbo].[JobOrder].[jobOrderID] in (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)

group by CAST([BHDMQA].[dbo].[JobOrder].[customTextBlock5] AS NVARCHAR(MAX))

In career portal code as follows:
assembleForGroupByWhereIDs: function assembleForGroupByWhereIDs(fields, orderByFields, start, count, jobs) {

				return '?where=' + _this3.requestParams.whereIDs(jobs, false) + '&groupBy=' + fields + '&fields=' + fields + ',count(id)&count=' + count + '&orderBy=+' + orderByFields + ',-count.id&start=' + start;
				
            },

Expected behavior

Need to work group by for customTextBlock text datatype

Steps to replicate behavior

1. Add customeTextBlock in side bar and check data will come or not based on these data types value
   

Screenshots

Current

Expected
Need to work group by based on customTextBlock

[harigkp]

Based on this issue I am unable to make sidebar for this customTextBlock5 i.e Brand offering in career portal(angularjs)

Thanks,
Hari

[pbearne]

@harigkp You might not be able to do that with the public API. This is a feature available in Matador Jobs ask your, client, if they have looked at Matador Jobs for their job broad

[charlesabarnes]

customTextBlocks can not be used in a groupBy in general I believe. I suggest migrating this data to a customText field