Sorting a list based on the average value of field under associated table

Question

There are two tables "items" and "ratings". Items can have many ratings.

Currently I am trying to retrieve a sorted collection of "items" based on the rating. So, I require to sort the collection according to average of values in rating.

Thus, I implemented this query.

SELECT items.id, items.description, avg(ratings.value) as average
 FROM `items` 
INNER JOIN `ratings` ON `ratings`.`item_id` = `items`.`id` 
WHERE `items`.`type` IN ('Book') 
GROUP BY ratings.item_id 
ORDER BY average desc;

Output(I have not pasted the whole description, since it is too long to fit):
    +-----+----------------------------+---------+
    | id  | description                | average |
    +-----+----------------------------+---------+
    | 241 | Into the intrigue and vio  |  3.0000 |
    | 242 | Teams can be a driving fo  |  2.0000 |
    | 243 | NULL                       |  5.0000 |
    | 244 | In Temptations of the Wes  |  1.0000 |
    | 245 | NULL                       |  4.0000 |
    +-----+----------------------------+---------+

Here, the items did not come out sorted accordingly.

Now, when I removed the description from selection, the results were coming out correct.

So, assuming that the problem was occurring due to length of the text field, I limited the description field length to a lower value. Here, the query is working properly as well. (After trying out multiple values, it figured the breakpoint it at 512)

SELECT items.id, left(items.description, 512), avg(ratings.value) as average
 FROM `items` 
INNER JOIN `ratings` ON `ratings`.`item_id` = `items`.`id` 
WHERE `items`.`type` IN ('Book') 
GROUP BY ratings.item_id 
ORDER BY average desc;

Output (For smaller trimming value):

+-----+----------------------------+---------+
| id  | left(items.description,25) | average |
+-----+----------------------------+---------+
| 243 | Into the intrigue and vio  |  5.0000 |
| 245 | Teams can be a driving fo  |  4.0000 |
| 241 | NULL                       |  3.0000 |
| 242 | In Temptations of the Wes  |  2.0000 |
| 244 | NULL                       |  1.0000 |
+-----+----------------------------+---------+

So, is this a bug or an expected behavior or am I missing out on something ?

How long was the description text field originally? And it would also help if you posted an example of the differing output for both queries. — Luke, Aug 12 '16 at 13:58
@Luke : Description is a field type "text" so there are long paragraphs in it. Also, I have updated the output. — Sudhir Shrestha, Aug 12 '16 at 14:15

score 0 · Answer 1 · answered Aug 12 '16 at 10:25

0

Have you tried grouping on these columns:

SELECT items.id, items.description, avg(ratings.value) as average
FROM `items` 
INNER JOIN `ratings` ON `ratings`.`item_id` = `items`.`id` 
WHERE `items`.`type` IN ('Book') 
GROUP BY items.id, items.description
ORDER BY average desc;

answered Aug 12 '16 at 10:25

verhie

1,298
1
7
7

No, this is returning the list sorted descending based on "items.id" – Sudhir Shrestha Aug 12 '16 at 10:37
so when your query says 'ORDER BY average desc' you tell me it is not sorted by average? – verhie Aug 12 '16 at 10:59
please look at the select statement, average is just a namespace "avg(ratings.value) as average" – Sudhir Shrestha Aug 12 '16 at 11:06
yes, that value holds the average rating for each item_id. and sorting on it, will sort your results on that value. That is your question, isn't it? So the item with highest average rating will be first result returned. – verhie Aug 12 '16 at 12:05
Yes, it is supposed to work that way but due to a text field "description", the returned collection is not returned in right order. After playing out a bit with the code, i figured trimming down the length of description, the query works fine. So, I am asking if this is a bug or it needs to be done some other way. – Sudhir Shrestha Aug 12 '16 at 13:37

score 0 · Accepted Answer · edited May 23 '17 at 12:00

Okay, so I started looking into the use of text fields in SQL, and as you suspected this is the problem. Text fields are something that you should avoid using in SQL because of the fact future versions of SQL will not support them. Coming straight from Microsoft's documentation..

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

The problem that you are having is that text in a text field is stored out of row when the length of the text surpasses some value. And it appears this is what has happened with your text field. For some reason this is blowing up the query processor or something else behind the scenes and resulting in unexpected results from your query.

To fix this, if you have access to modify the data type for the description column, you should switch it to a varchar of whatever length you need. As long as the data in the column is less than 8000 characters you should have your problem solved. If not, you will need to split the column up into multiple varchar columns and place smaller pieces of the text into each.

I'm posting a link to a related discussion below on text fields in SQL. Hope it helps!

SQL Server Text type vs. varchar data type

Thanks @Luke, there should have been be some deprecation log, can get easily lost in these situations. Luckily I am just starting out on a fresh project so don't have to worry about migrating data. I will change the field type or even use postgreSQL instead :D — Sudhir Shrestha, Aug 13 '16 at 04:42

Sorting a list based on the average value of field under associated table

2 Answers2