Order By Expression in Select DISTINCT Causing Error with HasDetail Field

[mohamed-shokrani]

I’m encountering an issue with an auto-generated query in Serenity when using SELECT DISTINCT and trying to filter by a custom field called HasDetail. The system-generated query looks like this:
SELECT DISTINCT
(Select count() from ReceiptDetails where T0.ReceiptId = ReceiptDetails.ReceiptId) AS [HasDetail]
FROM [dbo].[Receipts] T0
ORDER BY (Select count() from ReceiptDetails where T0.ReceiptId = ReceiptDetails.ReceiptId)
This causes the error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I want the query to be like this instead, which uses the alias in the ORDER BY clause:

SELECT DISTINCT
(Select count(*) from ReceiptDetails where T0.ReceiptId = ReceiptDetails.ReceiptId) AS [HasDetail]
FROM [dbo].[Receipts] T0
ORDER BY [HasDetail]
Is there a way to override the default behavior in Serenity to make the ORDER BY clause point to the HasDetail alias instead of the full expression?

Here is the relevant part of my code:
[DisplayName("Has Detail")]
[Expression("(Select count(*) from ReceiptDetails where T0.ReceiptId = ReceiptDetails.ReceiptId )")]
public Int16? HasDetail
{
get => fields.HasDetail[this];
set => fields.HasDetail[this] = value;
}
Thanks in advance for your assistance!

[LSNicholls]

Hi @mohamed-shokrani ,

I tried to repro your problem without success. I think it is because I don't know how you are "system-generating" the query with DISTINCT. I tried a number of different ways to set the order of a grid to a calculated column defined like yours, and I didn't get an error. But none of those methods generated a DISTINCT clause, either.

FWIW here is what I used (in UserRow.cs):

   [DisplayName("# Roles"), ReadOnly(true), Expression("(select count(*) from UserRoles where T0.UserId = UserRoles.UserId)")]
   public int? NumberRoles { get => fields.NumberRoles[this]; set => fields.NumberRoles[this] = value; }

I did get a TOP clause, as expected, which you don't show in your generated query. So, this leaves me with questions:

1. Just in case, what is your backend? I am testing with SQL Server.
2. What version of Serenity?
3. If this generated query is not supplying the contents of a grid, what is it doing/where are you generating it? Is this, for example, in a custom List Handler and you've added DISTINCT in the PrepareQuery for some reason? And where is the ORDER BY instruction coming from in your actual case?

Item 3 is the most likely concern. Assuming so, however you're doing this, I'm guessing that whether it turns out you really need DISTINCT or not, there is a better way to get your count value that will resolve the problem, considering that an in-line select like this is not usually the best performance.

Consider writing a view that gives a count of roles per user, so that only one additional SELECT is being performed rather than one per row. I will demonstrate what this would look like below, by adding the single extra SELECT in-line, but this in-line SELECT could easily be translated into a view so you can left join in your *row.cs definition. The ordering clause, wherever and however applied, will then probably work as you want -- and it will all run faster, too :-) :

select distinct -- I would still write the query this way even if it turns out you do NOT need distinct
   T0.[UserId] as [UserId],
   T0.[Username] as [Username],
   -- more fields here... 
   T0.[IsActive] as [IsActive],
   T0.[LastDirectoryUpdate] as [LastDirectoryUpdate],
   NumberRoles,
   -- more fields here... 
from [Users] T0 
left join 
(select count(*) as NumberRoles, UserId from UserRoles group by UserId) jRoleCount
on T0.UserId = jRoleCount.UserId
order by [NumberRoles];

Regarding whether or why you need DISTINCT... tell me more and I'll offer an alternative for this as well, if I think there is one that might be a more performant choice from the SQL point of view.

[mohamed-shokrani]

Hi @LSNicholls
thank u for your response the answers for your questions
1- I am also using sql server
2 - serenity version 5.0.36
3 - view provided image - and i did not even override prepareQuery method

the quick fix for me in this situation just disable filtering in the col and add quick filter for the field
although i do not know why it wroks fine with quick filter it suppose to have the same result.

[LSNicholls]

@mohamed-shokrani, thank you for the screen shot. Now I understand how it is happening. And thank you for answering my questions.

As to why it is different with quick filter, I will have to guess, because I don't have serenity v5: the quick filter is probably doing a separate query, possibly using SELECT DISTINCT but only selecting this one column, so instead of the ORDER BY clause using the nested select it can just use the ordinal number like this:

SELECT DISTINCT
(select count(*) from UserRoles where T0.UserId = UserRoles.UserId) AS [NumberRoles]
FROM [Users] T0 
ORDER BY 1

Alternatively, it might always use the same, meaningless alias for the column for the quick filter, so that it can use that alias in the ORDER BY column, like this:

SELECT DISTINCT
(select count(*) from UserRoles where T0.UserId = UserRoles.UserId) AS  QuickFilterValue
FROM [Users] T0 
ORDER BY QuickFilterValue

Or, even more likely, it doesn't use an ORDER BY clause at all when it gets the one column for the quick filter, since SQL Server will usually order by the one column in the results in this case.

You can verify this by debugging and looking to see what SQL is produced.

I am not sure why the Excel-style filtering select would use the ORDER BY and the quick filter select does not, or why it wouldn't use the alias; maybe this is because ordering by an alias or by the ordinal column number isn't supported for all possible SQL back-end database engines.

But it doesn't really matter; although I am glad you already have a quick fix, I still think that the proper fix would be to fix your *Row.cs file so that, instead of doing an in-line select as your expression, you join with a view that handles this aggregation properly in one SELECT. This way, things will run faster and you should be able to allow Excel-style filtering on the column!