Rails Active Record, how to have an additional field parameter in the result of a query?

Question

I actually have this model:

class Role < ActiveRecord::Base
  acts_as_authorization_role

  def self.all_join_wisp
    self.connection.select_all("SELECT roles.*, wisps.name AS wisp_name FROM roles LEFT JOIN wisps ON wisps.id = roles.authorizable_id")
  end
end

the method all_join_wisp does almost what I want, it adds the field wisp_name, except it return a hash and not an active record object.

Is there a way to retrieve an active record object instead?

The Role model does not have belongs_to and the wisp model does not have has_many :roles , I guess it was done for flexibility or something (I did not develop the application I'm working on).

Edit: Solution implemented

Solution implemented here: https://github.com/nemesisdesign/OpenWISP-Geographic-Monitoring/blob/287861d95fffde35d78b76ca1e529c21b0f3a54b/app/models/role.rb#L25 Thanks to @house9

It returns `array of hash` not just `hash`. You are using `raw` query so you can't get active record relation. There is no way get it unless you define associations. — Rahul Tapali, Mar 18 '13 at 18:04

score 2 · Accepted Answer · answered Mar 18 '13 at 18:17

2

you can use find_by_sql - you get back what looks like an activerecord object but isn't really, it will have attributes from your query but they will all be string data types instead of the real types, often this is ok

def self.all_join_wisp
  self.find_by_sql("SELECT roles.*, wisps.name AS wisp_name FROM roles LEFT JOIN wisps ON wisps.id = roles.authorizable_id")
end

then

list = Role.all_join_wisp
list.each do |x|
  puts x
  puts x.inspect
  puts x.id # the role id 
  puts x.id.is_a?(Integer) # false
  puts x.id.is_a?(String) # true
  puts x.name # the role name
  puts x.wisp_name # wisp name, NOTE: Role does not really have this attribute, find_by_sql adds it
end

answered Mar 18 '13 at 18:17

house9

20,359
8
55
61

it works, I can't figure out why when I tried this in the past I didn't get the same result. – nemesisdesign Mar 22 '13 at 14:04
I used this solution in the end because using joins and select methods does not look like a real advantage than writing the entire query as a lot of raw SQL is written anyway. You can see it here! :-) https://github.com/nemesisdesign/OpenWISP-Geographic-Monitoring/blob/287861d95fffde35d78b76ca1e529c21b0f3a54b/app/models/role.rb#L25 – nemesisdesign Mar 22 '13 at 16:30

score 1 · Answer 2 · answered Mar 18 '13 at 18:37

1

model

class Role < ActiveRecord::Base

      def self.do

        joins("LEFT JOIN wisps w ON
                w.id = roles.id").select("roles.*,
                                          wisps.name AS wisp_name")    

      end

    end

controller

@roles = Role.do

@wisps = @roles.map {|i| i.wisp_name}

view

<%= @wisps %>

answered Mar 18 '13 at 18:37

beck03076

3,268
2
27
37

1

the correct way is: joins("LEFT JOIN wisps ON wisps.id = roles.authorizable_id").select("roles.*, wisps.name AS wisp_name") – nemesisdesign Mar 22 '13 at 14:06
You should use alias in select statement – Stanislav Kr. Nov 17 '17 at 19:39

Rails Active Record, how to have an additional field parameter in the result of a query?

Edit: Solution implemented

2 Answers2