Order by on a Count in another table

Question

I wanted to sort a table of items by counting the positive evaluations(another table) for that item. This query will be executed in the controler: item action: index

Item:
  id
  nome

Avaliacao
  id
  item_id
  avaliacao[0 negativo, 1 positivo]

For example:

Banda 1 - <15 positivos> <3 negativos>
Banda 2 - <10 positivos> <30 negativos>
Banda 3 - <5 positiva> <0 negativas>
Banda 4 - <0 positiva> <0 negativas>
Banda 5 - <0 positiva> <0 negativas>
Banda 6 - <0 positiva> <0 negativas>
Banda 7 - <0 positiva> <0 negativas>
...

Answer 1

Taking advantage of @bfavaretto’s reply, in case you have Avaliacao one belongs_to :item, you should do the following for the ruby-on-Rails:

Item.joins(:avaliacaos).group("avaliacaos.item_id").order("sum(avaliacaos.avaliacao) desc")

Otherwise it will be necessary to iterate on the template elements Item

items = Hash.new
Item.all.each do |item|
  items[item.id] = Avaliacao.where('item_id = ? AND avaliacao = ?', item.id, 1).count
end
maiores_avaliacoes = items.sort_by { |k,v| v}.reverse

Where maiores_avaliacoes will be a vector of pairs in which the first element is the key and the second the number of positive evaluations, i.e. maiores_avaliacoes[0] return a vector with two positions where the position 0 will be the item_id of the item with more positive reviews and the position 1 will be the number of positive reviews that has that item.

This should only return items with positive reviews.

---

How to count the number of positive and negative reviews

Due to my inability to consult SQL, I won’t know which query should be made to return the number of positive or negative evaluations (although I suppose that one should use the count instead of sum). However, it is possible to achieve what you want by modifying the second example a little bit, as follows:

avaliacoes = Hash.new
Item.all.each do |item|
  avaliacoes[item.id] = [Avaliacao.where('item_id = ? AND avaliacao = ?', item.id, 0).count, Avaliacao.where('item_id = ? AND avaliacao = ?', item.id, 1).count]
end
Item.all.each do |item|
  puts item.nome + " - <" + avaliacoes[item.id][1] + " positivas> <" + avaliacoes[item.id][1] + " negativas>\n"
end

This prints the data on the console the way you want it, remembering that index 1 counts the number of ratings positive, while index 0 accounts for the number of valuations negative, saving them in a Hash using item.id.

Answer 2

The base is a query with JOIN between the two tables. You must also group by item, and sort by the sum of the evaluations:

SELECT Item.id, Item.nome, SUM(Avaliacao.avaliacao) AS positivos
FROM Item
INNER JOIN Avaliacao
ON Avaliacao.item_id = Item.id
GROUP BY Item.id
ORDER BY SUM(Avaliacao.avaliacao) DESC