4

Suppose to have the following table:

 Product(id:integer, category:string, cost:float)

And you want to perform this query with activerecord:

Product.select("cost + 1000 AS incremented_cost").group(:category).sum('incremented_cost')

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "incremented_cost" does not exist

The example is quite simplified, however it well represents the problem: how can I assign an alias to a modified field and the use it in an aggregate function?

My goal is to return an Activerecord_Relation where it is possible to compute aggregations on a alias field, possibly on a group. The problem is that "grouping" is conditional and I would like to keep the data pipeline.

select method does not work because it does not change the fields names of a relation. If you use "AS" it is possible to access the alias (see link). However it does not work in aggregate functions. See: http://apidock.com/rails/v4.1.8/ActiveRecord/QueryMethods/select

Using #inject it is not a good solution for me, I don't want to deal with arrays.

For instance this code gives the right result, but it's not a good solution for me:

Product.select("cost + 1000 AS incremented_cost").inject(0) do |acc, record|
   acc + record.incremented_cost
end
Andrea
  • 116
  • 1
  • 9
  • 1
    just do `Sum(cost + 1000) As incremented_cost` with a `group` if needed. – engineersmnky Mar 16 '15 at 14:55
  • Please, can you be more specific? – Andrea Mar 16 '15 at 14:57
  • Sure `Product.select("Sum(cost + 1000) As incremented_cost")` if you need additional columns they will need to be in the `select` statement and also the `group` statement e.g. `Product.select("Sum(cost + 1000) As incremented_cost, name").group("name")` – engineersmnky Mar 16 '15 at 15:06
  • Thanks! Unfortunately it is not that easy: 1) After `.select` in this wait I can't use `first` without calling `to_a` before. 2) I have kind of a pipeline so incremented_cost for every record -> group -> sum. I can't easily change the order. – Andrea Mar 16 '15 at 15:19
  • `select` still returns an `ActiveRecord::Relation` so i am not sure why you would have to call `to_a` but maybe try fleshing out your example a bit more since the obvious solutions based on your question do not suffice. [See Here for Docs on `#select`](http://apidock.com/rails/ActiveRecord/QueryMethods/select) Not this line **"If an alias was specified, it will be accessible from the resulting objects: `Model.select('field AS field_one').first.field_one # => "value"` "** – engineersmnky Mar 16 '15 at 15:59
  • @engineersmnky thanks for the suggestion. The thing is, how to modify a the value of a column (e.g. increment by 1000) and then perform an aggregation of the new column. – Andrea Mar 16 '15 at 19:55
  • That is exactly what I presented `cost + 1000` will add `1000` to every cost then `sum` will sum these so `sum(cost +1000)` using costs of `10,20,30` would be `3060` because `1010 + 1020 + 1030 = 3060`. This part is just standard SQL nothing railsy about it. – engineersmnky Mar 16 '15 at 20:23
  • yep, but the aggregation has to occur after. The goal, as specified in the question: is aggregate "alias" column. I am not even sure it is possible. – Andrea Mar 17 '15 at 06:40
  • 1
    @Andrea, it's not quite clear what you are trying to achieve with *the aggregation has to occur after*. could you update your question accordingly? With what you described currently, something like: `Product.select("sum(cost + 1000) as increased_cost").first.increased_cost` returns the `sum` of *increased* by 1000 `cost`s – Paweł Dawczak Mar 17 '15 at 08:29
  • Additionally, please check the other [answer](http://stackoverflow.com/a/16904721/4381282). Hope that helps! – Paweł Dawczak Mar 17 '15 at 08:30
  • Thanks @PawełDawczak! I updated the question, now it should be clearer. The problem with this approach is that it does not allow grouping using the "alias" field name. – Andrea Mar 17 '15 at 08:32
  • Let's consider something like this then: `Product.select("sum(cost + 1000) as increased_cost, category").group(:category).each { |product| p "#{product.category} - #{product.increased_cost}" }`. Please take a note, the `sum` is still calculated in `select`. Does this produce output you expect? Hope that helps! – Paweł Dawczak Mar 17 '15 at 08:46
  • In fact, you can't do `.sum("aliased_column")`, but you can do something like: `p = Product.group(:category)`, `p = p.select("sum(cost + 1000) as increased_cost, category")`, and follow this with `p.each { |product| p "#{product.category} - #{product.increased_cost}" }`. Basically, you can chain them, but you should try to replace `sum` with `select` with aggregation. Hope it gives some hint to your problem! – Paweł Dawczak Mar 17 '15 at 08:55
  • 1) `p = Product.group(:category)` it's not possible: you need to specify an aggregation function 2) I would prefer to not use arrays method to exploit better the database – Andrea Mar 17 '15 at 09:11
  • @Andrea, which version of Rails do you use? – Paweł Dawczak Mar 17 '15 at 09:15
  • I am using `activerecord 4.2.0` – Andrea Mar 17 '15 at 09:55
  • Then, kindly, I can't agree with you. `p = Product.group(:category)` is perfectly valid, and at that point, what is stored in `p`, are not products, but [`ActiveRelation`](http://api.rubyonrails.org/classes/ActiveRecord/Relation.html) which you can chain with method as described in my comment. According to my example the database is queried once you perform `.each`. Until that, you can freely modify query. I appreciate the formatting in comments is odd, should I create an answer? – Paweł Dawczak Mar 17 '15 at 10:00
  • sorry @PawełDawczak, my bad I am using `4.1.9`. I don't like `#each` because after that I can't chain any other activerecord method such as `sum` / `order` etc.. I don't want to switch to arrays method unless 100% necessary (which might be). Thanks for the suggestion! – Andrea Mar 17 '15 at 10:23
  • @Andrea - no problem! `ActiveRecord::Relation` has been introduced in Rails 3, so unless you use Rails 2 - you are good to go! `each` is an example of *terminating*, and performing the real query. Could you show an example of *how* you would like use the aggregated values, so we are able to suggest how to proceed with it? – Paweł Dawczak Mar 17 '15 at 10:29
  • As with standard SQL you should be able to aggregate using the actual expression e.g. `Product.select("(cost + 1000) as incremented_cost").sum("(cost + 1000)")` this is valid SQL but I don't think you can chain off of `aggregate functions` because of query syntax issues. – engineersmnky Mar 17 '15 at 13:21

0 Answers0