How to Sort on a aggregated joined value

Posts   
 
    
gabrielk avatar
gabrielk
User
Posts: 231
Joined: 01-Feb-2005
# Posted on: 17-Oct-2015 15:16:39   

Hi,

I was trying to achieve the following with LLBGen:

Tables: Restaurant & Rating, linked via Restaurant.RestaurantId == Rating.RestaurantId


select top(1) *, (select AVG(1.0*Rating.Score) from Rating where Rating.RestaurantId = Restaurant.RestaurantId) as Score
from Restaurant
order by Score desc

(1.0* is done to get a decimal average instead of integer)

I want to retrieve the Dish with the heighest average rating. I know assume it's something with DbFunctionCall, but I've not used that enough to understand how to apply it in this situation.

LLBLGen 4.2, SelfServicing

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Oct-2015 07:12:30   

Actually, your second field should be a ScalarQuery instead of a DBFunctionCall. This ScalarQuery would be insdide a DynamicList. What code do you have so far?

See ScalarQueryExpressions in the documentation to see how to user ScalarExpressions, once you are done with that, you just include that scalar field in your SortExpression.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Oct-2015 09:44:33   

If possible try to use QuerySpec or Linq, as you then also open up the option to e.g. use resultset caching and other things available like async, in the higher-level query api's and not in the low-level API. QuerySpec offers extension methods to produce scalar queries as well, which can help, if you want to use the low-level api.

Frans Bouma | Lead developer LLBLGen Pro