I’ve an entity with lookup field - the lookuped value is GUID. This field is on the screen with defined DataLookup value. When I want to filter the screen via that field I got error:
Server error occurred. Please check server log for more details:
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Invalid column name 'refCurrentProjectId'.
Stack trace:
2021-08-17 16:15:56,857 [32] ERROR Origam.ServerCore.Controller.AbstractController - Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Invalid column name 'refCurrentProjectId'.
System.Data.SqlClient.SqlException (0x80131904): Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Invalid column name 'refCurrentProjectId'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Origam.DA.Service.AbstractSqlDataService.ExecuteDataReader(DataStructureQuery query, IPrincipal principal, String transactionId) in D:\a\1\s\origam-source\Origam.DA.Service\AbstractSqlDataService.cs:line 1617
at Origam.DA.Service.AbstractSqlDataService.ExecuteDataReaderInternal(DataStructureQuery query)+MoveNext() in D:\a\1\s\origam-source\Origam.DA.Service\AbstractSqlDataService.cs:line 1698
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at Origam.ServerCore.Controller.UIServiceController.StreamlineFilterListValues(IEnumerable`1 fullReaderResult) in D:\a\1\s\origam-source\Origam.ServerCore\Controller\UIServiceController.cs:line 648
at CSharpFunctionalExtensions.ResultExtensions.Bind[T,K,E](Result`2 result, Func`2 func)
at Origam.ServerCore.Controller.UIServiceController.<>c__DisplayClass60_0.<GetFilterListValues>b__0() in D:\a\1\s\origam-source\Origam.ServerCore\Controller\UIServiceController.cs:line 610
at Origam.ServerCore.Controller.AbstractController.RunWithErrorHandler(Func`1 func) in D:\a\1\s\origam-source\Origam.ServerCore\Controller\AbstractController.cs:line 87
ClientConnectionId:074d7ec8-e7d4-446f-be41-a319da94c45f
Error Number:144,State:1,Class:15
Unfortunately lookup fields have a limited functionality in lazy-loaded lists. Are you sure it is just the filter? Can you please append the content of the http request that causes this error?
Now I see you are trying to get a list of values for the filter based on a lookup field. So that is unsupported as the way we get them is by using a GROUP BY to get distinct values.
The error is shown only when you want to filter via that field. We have a screen with several lookups fields and the error is on every field. This was working on Flash.
Yes because Flash was not using pagination which now lets us work with far more data than before. If you turn off lazy-loading it will work again but this will only work well for small data sets. Maybe we could figure out how to do this without using GROUP BY.
Can you please post the whole payload from the request as a text block?
That was whole payload - now as text. The affected screen is displaying around 1000 records in master screen and there are attached several screens as details.
@tvavrda do you have any plan to support this use case? Turning off lazy loading as a workaround is step back for fast screen load. We have several mandatory screens with lazy loading, where we are using lookup fields and it is much more slower without lazy loading than in flash with lazy loading on.
@koki We could support sub-SELECTs even in lazy loaded screens but it will definitely come with a performance penalty. There is no way how to work around it.
If you would remodel the lookup fields into inner joins in each data structure where they are used, you would get 5x performance than if we would support it and you left the model without changing.
What do you think? Will you consider remodelling (so we do not have to change anything and you get the best performance) or do you want us to fix it so you do not have to work but you might get slow app?
Here you can see the difference. Please note the 81:19 costs in favour to the inner join.
Actually performance is not an issue for us. But we have used lookup field in many datastructures across whole model. So sub-select is an option for us.
@jsusen let’s implement it then. I propose solving the GROUP BY problem for subselects in the following way.
Today the query looks like this:
SELECT
OneField,
SecondField,
(SELECT TOP 1 Something FROM AnotherTable ...) AS ThirdField
FROM
ATable
We obviously cannot group by the ThirdField. Let’s detect when a lookup field exists in the query. If it does AND there is grouping, we will enclose the whole query into another SELECT, like this:
SELECT COUNT(OneField), SecondField, ThirdField FROM (
SELECT
OneField,
SecondField,
(SELECT TOP 1 Something FROM AnotherTable ...) AS ThirdField
FROM
ATable
) AS Query
GROUP BY SecondField, ThirdField
When this is solved we can also enable grouping by lookup fields in the Column configuration dialog.
