Блог

В моей модели у меня есть пара запросов, которые можно использовать (и повторно использовать) один за другим. Одна из них должна агрегировать суммы. Это отлично работает на SQLite и выдает ошибку в Postgres:

 ActiveRecord::StatementInvalid (PGError: ERROR:  column "entries.date" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT sum(case when joint = false then amount else amount / 2 end) as total, sum(case when joint = false then amount else 0 end) as sum_personal, sum(case when joint = true and user_id = 1 then amount / 2 else 0 end) as sum_user_joint, sum(case when joint = true and user_id = 2 then amount / 2 else 0 end) as sum_partner_joint  FROM "entries" WHERE (1 = entries.user_id OR (2 = entries.user_id AND entries.joint = 't')) AND ('2011-04-01' <= entries.date AND entries.date <= '2011-04-30') AND (amount_calc > 0 AND compensation = 'f') ORDER BY date asc)
  

Соответствующая часть Model.rb

   # all entries of one month
  def self.all_entries_month(year, month, user_id, partner_id)
    mydate = Date.new(year, month, 1)

    where(':user_id = entries.user_id OR (:partner_id = entries.user_id AND entries.joint = :true)', {
        :user_id => user_id,
        :partner_id => partner_id,
        :true => true
    }).
    where(':first_day <= entries.date AND entries.date <= :last_day', { 
        :first_day => mydate,
        :last_day => mydate.at_end_of_month
    })
  end

  def self.income
    where('amount_calc > 0 AND compensation = ?', false)
  end

  def self.cost
    where('amount_calc <= 0 AND compensation = ?', false)
  end

  def self.order_by_date
    order('date asc')
  end

  # group by tag and build sum of groups named group_sum
  def self.group_by_tag(order)
    group('tag').
    select('tag, '  
           'sum(case when joint = "f" then amount else amount / 2 end) as tag_sum'
          ).
    order('tag_sum '   order)
  end

  def self.multiple_sums(user_id, partner_id)
    case ActiveRecord::Base.connection.adapter_name  
      when 'SQLite'
        select('sum(case when joint = "f" then amount else amount / 2 end) as total, '  
               'sum(case when joint = "f" then amount else 0 end) as sum_personal, '   
               'sum(case when joint = "t" and user_id = '   user_id.to_s   ' then amount / 2 else 0 end) as sum_user_joint, '   
               'sum(case when joint = "t" and user_id = '   partner_id.to_s   ' then amount / 2 else 0 end) as sum_partner_joint ' 
        )
      when 'PostgreSQL'
        select('sum(case when joint = false then amount else amount / 2 end) as total, '  
               'sum(case when joint = false then amount else 0 end) as sum_personal, '   
               'sum(case when joint = true and user_id = '   user_id.to_s   ' then amount / 2 else 0 end) as sum_user_joint, '   
               'sum(case when joint = true and user_id = '   partner_id.to_s   ' then amount / 2 else 0 end) as sum_partner_joint ' 
        )
    else
      raise 'Query not implemented for this DB adapter'
    end  
  end
  

Контроллер

 # get all entries of given month
@cost = Entry.all_entries_month(@year, @month, current_user.id, current_partner.id).cost

# group cost by categories
@group_cost = @cost.group_by_tag('asc')

# still need to sort by date
@cost = @cost.order_by_date

@calc_cost = @cost.multiple_sums(current_user.id, current_partner.id)[0]  
  

Как я могу изменить свой запрос multiple_sums, не нарушая другие запросы? Или мне нужно реализовать multiple_sums с нуля, не используя существующие?

Удалите предложение order by, которое в любом случае бесполезно, насколько я могу судить, потому что вы группируете в одну строку.

И, пожалуйста, переформатируйте ваши запросы так, чтобы они были видны и читабельны.