1

Hi there, I’m currently developing an abstract data layer. I use a custom Java Action for the data retrieves. I’m not sure if this issue is connected to the one I already created an issue for, but since I turned security off for the project I assume this must be something different.  We use a PostgreSQL database. I’d like to perform the following (working) query in postgres to be used in OQL in Mendix to retrieve the same data. This is the working PostgreSQL query: select date_part('WEEK', state_leaves_draft) as category, State as subcategory, count(State) as value from "requestfulfilment$request" group by category, subcategory and yields: [{ "category" : 45, "subcategory" : "Initiated", "value" : 3 }] This is correct and expected. Here an example OQL I performed which worked: select * from RequestFulfilment.Request yields [ { "Number": 97, "State_Leaves_Draft": "Tue Nov 05 22:17:25 CET 2019", "State": "Initiated", ... }, { "Number": 98, "State_Leaves_Draft": "Wed Nov 06 10:09:13 CET 2019", "State": "Initiated", ... }, { "Number": 102, "State_Leaves_Draft": "Fri Nov 08 09:49:58 CET 2019", "State": "Initiated", ... } ] There are more columns, but those are not relevant for this example. The data returned is correct. Here another OQL query which works select DATEPART(WEEK, State_Leaves_Draft) as category, State as part from RequestFulfilment.Request yields: [ { "part": "Initiated", "category": 45 }, { "part": "Initiated", "category": 45 }, { "part": "Initiated", "category": 45 } ] I also tested the `COUNT` function as well: select COUNT(State) as value from RequestFulfilment.Request yields [{"value":3}] Now, if I put this all together and use the group by the query looks like this: SELECT DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value FROM RequestFulfilment.Request GROUP BY category, subcategory   This query throws a warning and an exception: DataStorage_QueryHandling: - Executing query with old implementation due to detected exception in new implementation: No access rights for expression [category] An error occurred: com.mendix.connectionbus.ConnectionBusRuntimeException Message: 'requestfulfilment$request' is not a valid entity type. Stacktrace: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.connectionbus.ConnectionBusRuntimeException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:386) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.connectionbus.ConnectionBusRuntimeException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.connectionbus.ConnectionBusRuntimeException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.connectionbus.ConnectionBusRuntimeException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156) Caused by: com.mendix.connectionbus.ConnectionBusRuntimeException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): select DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value from RequestFulfilment.Request group by category, subcategory at com.mendix.connectionbus.RequestAnalyzer.doRequest(RequestAnalyzer.java:63) Caused by: 'requestfulfilment$request' is not a valid entity type., exception occurred on mapping the following query: SELECT DATEPART(WEEK, requestfulfilment$request.state_leaves_draft, sun.util.calendar.ZoneInfo[id="Etc/GMT+1",offset=3600000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]), State AS subcategory, COUNT(requestfulfilment$request.state AS subcategory) AS value FROM requestfulfilment$request GROUP BY category, subcategory at com.mendix.connectionbus.retrieve.query.mapping.QueryMapper.getMappedQueries(QueryMapper.java:276) Caused by: 'requestfulfilment$request' is not a valid entity type. at com.mendix.connectionbus.util.LegacyModel.$anonfun$getMetaObject$1(LegacyModel.scala:43) at scala.Option.getOrElse(Option.scala:189) at com.mendix.connectionbus.util.LegacyModel.getMetaObject(LegacyModel.scala:43) at com.mendix.connectionbus.DomainModelUtilImpl.getPersistableMetaObject(DomainModelUtil.scala:67) at com.mendix.connectionbus.DomainModelUtil$.getPersistableMetaObject(DomainModelUtil.scala:32) at com.mendix.connectionbus.DomainModelUtil.getPersistableMetaObject(DomainModelUtil.scala) at com.mendix.connectionbus.retrieve.query.mapping.MetaObjectInfo.getMetaObject(MetaObjectInfo.java:144) at com.mendix.connectionbus.retrieve.query.mapping.SelectColumnsMapper.getColumnLocation(SelectColumnsMapper.java:315) I also tried to GROUP BY column number instead of name, but this also didn’t work: SELECT DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value FROM RequestFulfilment.Request GROUP BY 1, 2 throws this: An error occurred: org.postgresql.util.PSQLException Message: ERROR: column "requestfulfilment$request.state_leaves_draft" must appear in the GROUP BY clause or be used in an aggregate function Position: 27 Stacktrace: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.basis.connectionbus.ConnectionBusException: An exception has occurred for the following request(s): InternalOqlTextGetRequest (depth = -1): SELECT DATEPART(WEEK, State_Leaves_Draft) as category, State as subcategory, COUNT(State) as value FROM RequestFulfilment.Request GROUP BY 1, 2 at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:386) ---   A GROUP BY on a regular column works. A GROUP BY with multiple columns works as well. A COUNT works. … it seems everything by itself works, but you cannot GROUP BY an OQL Function column? Some more infos. My Java code: @java.lang.Override public java.lang.String executeAction() throws Exception { // BEGIN USER CODE IOQLTextGetRequest request = Core.createOQLTextGetRequest(); request.setQuery(query); IContext context = getContext(); IDataTable data = Core.retrieveOQLDataTable(context, request); return JsonSerializer.serialize(context, data); // END USER CODE } and the relevant portion of the serializer: IDataTable dataTable = (IDataTable) o; IDataTableSchema schema = dataTable.getSchema(); List<? extends IDataColumnSchema> columnSchemas = schema.getColumnSchemas(); List<? extends IDataRow> rows = dataTable.getRows(); List<HashMap<String, Object>> result = new ArrayList<>(); rows.forEach(r -> { HashMap<String, Object> map = new HashMap<>(); result.add(map); columnSchemas.forEach(c -> map.put(c.getName(), r.getValue(context, c))); }); return new JSONArray(result).toString(); (using Mendix v8.4.1)

asked 2019-11-29

2 answers