2

In my controller I am trying to run a query to get all of the id's not referenced in another table like so:

@vlan_numbers = ActiveRecord::Base.connection.execute("SELECT pop_vlans.id, vlan_number FROM pop_vlans WHERE pop_vlans.id NOT IN (SELECT logical_interfaces.vlan_id FROM logical_interfaces) AND pop_id != " + @pop_id.to_s)

Then in my view I am trying to use collection_select to show these in a dropdown menu:

but the error I get is undefined method 'vlan_number' for [2, "2"]:Array where those values are just the first row of results from the query.

This is a diagram of the two tables involved:

logical_interfaces | pop_vlans
-------------------|-----------
     vlan_id-------|----->id
       ....        |  vlan_number

and the relationships in the models are:

pop_vlan.rb

belongs_to :logical_interface

logical_interface.rb

# no relationship defined

Update

This is how the form is generated:

<%= form_tag :controller => "circuit", :action => "update" %>
    # other inputs
    <%= select_tag options_for_select(@vlan_numbers) %>
    # other inputs
 </form>
martincarlin87
  • 10,848
  • 24
  • 98
  • 145

2 Answers2

4

You can use select with options_for_select(@vlan_numbers) instead of collection_select

results from ActiveRecord::Base.connection.execute doesn't get loaded into a model

Instead you could try YourModelName.find_by_sql(...) If you want to play with your model

UPDATE Assuming the name of your attribute you want this select_tag to populate is vlan_id so:

<%= form_tag :controller => "circuit", :action => "update" %>
  # other inputs
  <%= select_tag 'vlan_id', options_for_select(@vlan_numbers) %>
  # other inputs
</form>
j03w
  • 3,679
  • 1
  • 21
  • 15
  • thanks but using `<%= select options_for_select(@vlan_numbers) %>` gives the following error message - `wrong number of arguments (1 for 3)` – martincarlin87 Aug 14 '13 at 08:59
  • or `select_tag` may be… depends on how you're building your form if you are using `form_for` then `f.select :vlan_id, options_for_select(@vlan_numbers)` given that `f` is the instance of your form builder – j03w Aug 14 '13 at 09:11
  • do you mean `<%= select_tag options_for_select(@vlan_numbers) %>`? Using that, my select menu isn't populated with any options. – martincarlin87 Aug 14 '13 at 09:15
  • can you provide us some snippets on how you are generating your form? – j03w Aug 14 '13 at 09:25
  • @jo3w, I've updated the question, each input is just manually inserted into the view via an appropriate helper function such as `text_field` or `check_box` – martincarlin87 Aug 14 '13 at 09:31
  • yes, that's the one, it's *almost* perfect but for some reason the id and vlan_number have swapped, so the `id` is the option text and the `vlan_number` is the value when it should be vice versa, can't see why that's happened though? – martincarlin87 Aug 14 '13 at 09:47
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35416/discussion-between-j03w-and-martincarlin87) – j03w Aug 14 '13 at 09:51
1

ActiveRecord::Base.connection.execute returns Mysql2::Result not array of object as Arel/AR query does.

So it returns array of arrays containing pop_vlans.id, vlan_number. So have to use first or last in your collection_select instead of something.vlan_number

So you have to use options_for_select instead of collection_select like:

options_for_select(@vlan_numbers.collect{|v| v.first})
Community
  • 1
  • 1
Muntasim
  • 6,689
  • 3
  • 46
  • 69
  • thanks but I'm not quite sure what you mean, do you have an example? – martincarlin87 Aug 14 '13 at 09:00
  • sample output of `@vlan_numbers = ActiveRecord::Base.connection.execute("SELECT pop_vlans.id, vlan_number FROM pop_vlans WHERE pop_vlans.id NOT IN (SELECT logical_interfaces.vlan_id FROM logical_interfaces) AND pop_id != " + @pop_id.to_s)` could be [[1,3], [2,3], [4,5], [3,1]]. so if you use [[1,3], [2,3], [4,5], [3,1]] in collection_select you have to operate on array so you should not find `vlan_number` in array – Muntasim Aug 14 '13 at 09:18
  • I think I know what you mean now, so, next question would be, how to get the last member of each array? e.g. using your example, `3,3,5,1` but bearing in mind I would need the first member of each array to act as the value of the select. – martincarlin87 Aug 14 '13 at 09:21
  • @martincarlin87 see the api http://api.rubyonrails.org/classes/ActionView/Helpers/FormBuilder.html#method-i-collection_select – j03w Aug 14 '13 at 09:29