ActiveRecord deep nested query and computed column

Question

Here are the associations,

Publication : has_many exchange_rates, books and authors

Book belongs_to author, exchange_rate

Author belongs_to exchange_rate

I am trying to write a query to find books with one extra computed_column.

This is the query I have tried so far,

publication.books.joins(:exchange_rate, :author).select("books.*, (exchange_rates.exchange_ratio + (SELECT exchange_rates.exchange_ratio FROM exchange_rates WHERE exchange_rate_id = authors.exchange_rate_id)) AS computed_column")

My aim is to generate a column which computed from associated tables attributes.

That means,

book.exchange_rate.exchange_ratio + book.author.exchange_rate.exchange_ration AS computed_column

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

Add computed_column as an attr_accessor on Book

class Book
  attr_accessor :computed_column
end

Then each instance of Book in your query will have book.computed_column available

For reference: Access SQL computed columns through ActiveRecord

edited May 23 '17 at 10:24

Community

1
1

answered Jan 31 '15 at 07:04

Daniel Ma

646
5
10

`computed_column` is just a dummy column which contains computed value. No need of `attr_accessor` here. – Sourabh Upadhyay Jan 31 '15 at 08:49
The reason for the `attr_accessor` is so that Rails knows what to do with `computed_column` when it retrieves it in the SQL query. If your model doesn't have a column or accessor set up with that name, Rails will discard the column – Daniel Ma Jan 31 '15 at 09:56
We are not fetching a virtual column that contains computed value. It doesn't required `attr_accessor`. – Sourabh Upadhyay Jan 31 '15 at 13:33
Unfortunately then, I don't understand the nature of your question. I thought that was what you wanted to do. Would you mind explaining in more detail? – Daniel Ma Jan 31 '15 at 22:22
I want to fetch db columns and 1 extra which having the calculate value. Sort of `sum of two column`. – Sourabh Upadhyay Feb 02 '15 at 06:13
Do you need help with your rails code or your mysql query? – Daniel Ma Feb 02 '15 at 07:49
I see. I apologize for misunderstanding. Let me see if I can re-write my answer to get the query working – Daniel Ma Feb 02 '15 at 18:50
What currently happens when you try this? – Daniel Ma Feb 02 '15 at 19:38
When I tried the above command, got error that in `authors.exchange_rate_id` `authors` is undefined variable. – Sourabh Upadhyay Feb 03 '15 at 06:03

ActiveRecord deep nested query and computed column

1 Answers1