Forums (Obsolete)

Member

krejcik-email - 12/19/2013 3:15:33 AM

ORDER BY expression

Hello,

I would like to ask how to order documents according average Rating of the document?
I know there are DocumentsRatings (number of ratings) and DocumentRatingsValue (Sum of all ratings), but how can I easilly divide these numbers in Web Part Properties of for example Basic Repeater in ORDER BY EXPRESSION form?

Is it possible to make it in the form "ORDER BY EXPRESSION" or do I have to make some function called Ratings (which would count the final Rating of document) and which I will have to use then in this ORDER by Expression form?

Kentico Consulting

Kentico_RichardS - 12/19/2013 3:51:19 AM

RE:ORDER BY expression

Hi,

Thank you for your message.

I have been playing with the query inside my SQL MS and I was able to accomplish this by the following order by query on CMS_Document table:

order by (DocumentRatings / (DocumentRatingValue*2+1)) desc

which returned the document ordered by worst rating to best.

The reason I multiplied it by 2 is because of our rating system has 10 levels (its not that intuitive since on front page we use 5 stars, but if you check document properties you will see its doubled) and added 1 because you cannot divide by zero (there might be documents without any ratings)

Let me know how this works.

Kind regards,
Richard Sustek

Member

krejcik-email - 12/19/2013 4:37:04 AM

RE:ORDER BY expression

I tried to put it in "Order by expression form", but it didn´t worked, it preffered a document with 2 ratings, which had worse final rating than document with just 1 rating with highest value. I don´t know why..

Now I repaired it by using settings from the demo eccomerce site (I tried it before, but I didn´t enter data to form COLUMNS, so it didn´t worked):

Order by expression: Rating desc

Where condition: DocumentRatingValue > 0.5 AND SKUProductType != 'DONATION' AND NodeSKUID IS NOT NULL

Columns: SKUName, SKUDescription, SKUImagePath, SKUPrice, SKURetailPrice, SKUID, SKUEnabled, SKUSiteID, SKUDepartmentID, DocumentRatingValue, NodeAliasPath, DocumentSKUName, DocumentSKUShortDescription, DocumentRatingValue / NULLIF(DocumentRatings,0) as Rating

This works just fine for me, but thanks for your sollution anyway. :-)


Kentico Consulting	Kentico_RichardS - 12/19/2013 4:47:28 AM RE:ORDER BY expression Hi, Im glad your solution works:) I checked that only for a several documents that I had in disposal at the moment and it worked correctly, but it can have some mistakes in it. Thank you for sharing the solution. Kind regards, Richard Sustek