CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4rails-activerecord
Oleh Sobchuk
Oleh Sobchuk

Reputation: 3722

Custom joined SQL to ActiveRecord

DB - PostgreSQL Rails - 4

I have next SQL

sql = <<-SQL
  LEFT OUTER JOIN (
    SELECT SUM(id) AS sum_ids, some_key FROM second_models
    WHERE id < 10000
    GROUP BY some_key
  ) AS second_models ON first_models.id = second_models.first_model_id
SQL
record = FModel.joins(sql).last

record.sum_ids # DOESN'T WORK !

and I can see the record as ActiveRecord object, but can I get somehow field sum_ids which was built manually?

Upvotes: 0

Views: 86

Answers (1)

3limin4t0r
3limin4t0r

Reputation: 21160

The additional field is inside the join section. It is not selected by default and thus can't be read. When executing your statement you get something like the following SQL query:

SELECT first_models.*
FROM first_models
  INNER JOIN (
    SELECT SUM(id) AS sum_ids, some_key
    FROM second_models
    WHERE id < 10000
    GROUP BY some_key
  ) AS second_models
  ON first_models.id = second_models.first_model_id

The first select statement prevents the sum_ids field from being accessible in your object since it's never returned to Rails. You want to change SELECT first_models.* to SELECT *. This is simply done by specifying the following select:

record = FModel.select(Arel.star).joins(sql).last
record.sum_ids
#=> should now give you your value

You can also add your field specifically using the following method:

f_models = FModel.arel_table
record = FModel.select(f_models[Arel.star]).select('sum_ids').joins(sql).last

This should result in SELECT first_models.*, sum_ids.

Upvotes: 1

Related Questions