Ruby on Rails accessing MySQL results error

Question

In my view I send an ajax request to get the device_ports of a particular device.

Previously I used

def get_device_ports
  if params[:id] != ''
    @device_ports = Device.find(params[:id]).device_ports.all(:order => 'id ASC')
    output = '<option value="">Select Device Port...</option>'
    @device_ports.each do |device_port|
      output = output + '<option value="' + device_port.id.to_s + '">' + device_port.name + '</option>'
    end
    render :text => output
  else
    render :text => '0'
  end
end

Which worked one but now having changed my query I get an error undefined method 'name' for [268, "test-1"]:Array with 268 and test-1 being the id and name of the first row of results.

This is my updated code:

def get_device_ports
  if params[:id] != '' and params[:device_id] != ''
    # @device_ports = Device.find(params[:id]).device_ports.all(:order => 'id ASC')
    device_id = params[:device_id]
    # Need a list of ports that aren't in use or are multiuse
    @device_ports = ActiveRecord::Base.connection.execute('SELECT DISTINCT d.id, d.name FROM device_ports d LEFT OUTER JOIN circuits c ON c.physical_port_id = d.id WHERE (c.physical_port_id IS NULL AND d.device_id = ' + device_id + ') OR (d.multiuse = 1 AND d.device_id = ' + device_id + ') ORDER BY d.id ')
    output = '<option value="">Select Device Port...</option>'
    @device_ports.each do |device_port|
      output = output + '<option value="' + device_port.id.to_s + '">' + device_port.name + '</option>'
    end
    render :text => output
  else
    render :text => '0'
  end
end

I'm just not sure why I'm getting the error, I imagine it's something trivial but due to the amount of different NoMethodError questions it's hard to find an answer.

PinnyM · Accepted Answer · 2013-08-23 11:59:39Z

2

You are having this problem because you aren't using ActiveRecord as an ORM to wrap the object, but rather executing a query and working on the resulting series of arrays. I would recommend changing your query like so:

    @device_ports = Device.find(device_id).device_ports.includes(:circuits).
                           where('device_ports.multiuse = 1 OR circuits.id IS NULL').
                           order('device_ports.id').distinct

If you absolutely want to avoid ActiveRecord, then don't use id and name, but rather treat each record as an array:

output << %Q{<option value="#{device_port.first}">#{device_port.last}</option>}

UPDATE

I just noticed that you're using RoR-2. Although more painful, you can still use an ActiveRecord query like so:

    @device_ports = DevicePort.all(
                           :joins => "LEFT JOIN circuits c ON device_ports.id = c.devic_port_id",
                           :conditions => ['device_ports.device_id = ? AND (device_ports.multiuse = 1 OR c.id IS NULL)', device_id],
                           :order => 'device_ports.id',
                           :select => 'DISTINCT device_ports.id, device_ports.name')

edited Aug 23, 2013 at 11:59

answered Aug 23, 2013 at 11:43

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Durrant Over a year ago

I'd also recommend using rails form builder helpers for the select and options. Same basic issue - no point using rails if you don't use its facilities and hard-code everything. Might as well use php :)

martincarlin87 Over a year ago

I agree but due to how complex this query was I didn't think there was a 'Rails way' of doing it. Thanks for the help though.

Collectives™ on Stack Overflow

Ruby on Rails accessing MySQL results error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related