0

I have two classes - logical_interfaces and pop_vlans with a one-to-one relationship.

In the logical_interfaces table the foreign key is vlan_id which relates to the id column in pop_vlans.

In my controller I get a list of vlan_numbers (stored in pop_vlans like so:

@vlan_numbers = PopVlan.find_all_by_pop_id(@device.pop.id)

(@device.pop.id is the pop the pop_vlan belongs to which is the same as the device)

then in my view I generate a select from this collection:

<%= collection_select 'logical_interface', "vlan_id", @vlan_numbers, :id, :vlan_number %>

This is all fine but I don't want to fetch all the vlan_numbers, only the ones whose id is not referenced in the logical_interfaces table. i.e. only those numbers which are not already in use.

I am terribly sorry for the confusion as this is quite a long-winded question to ask something that is hopefully straight-forward.

I think it could be done with a manual SQL query but I have no idea if there are built in methods to allow this to be done 'the Rails way'.

I think the MySQL to achieve the opposite would be SELECT pop_vlans.id, vlan_number FROM pop_vlans LEFT JOIN logical_interfaces ON logical_interfaces.id = pop_vlans.id but I can't actually think how to adjust that query to find vlan_numbers that don't match.

TLDR

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

get all from the right table whose foreign key is not referenced in the left table

I am thinking that it might just be a lot easier to add a new column to the table to act as a flag if it's in use and use that as the condition in the query.

martincarlin87
  • 10,848
  • 24
  • 98
  • 145
  • sorry but if you have a `LEFT JOIN` query for your `one-to-one` two tables, it will return the entire rows, so what you call `opposite` is not very clear – epsilones Aug 13 '13 at 16:08
  • quickly saying, could it be something like `SELECT pop_vlans.id, vlan_number FROM pop_vlans WHERE pop_vlans.id, vlan_number NOT IN (SELECT pop_vlans.id, vlan_number FROM pop_vlans INNER JOIN logical_interfaces ON logical_interfaces.id = pop_vlans.id)` – epsilones Aug 13 '13 at 16:13
  • think you could be right @Newben, will give the above and your answer a try and see if they work, thanks for your help. – martincarlin87 Aug 13 '13 at 18:34
  • hmm get an error running that query manually but will try the below in the rails app. – martincarlin87 Aug 13 '13 at 18:35
  • just noticed a tiny error in your query, the last part should be `ON logical_interfaces.vlan_id = pop_vlans.id` but still getting an error – martincarlin87 Aug 13 '13 at 21:35

2 Answers2

1

if what I indicated as a remark is indeed the query you're looking for, I think you can try this 

subQuery = Pop_vlan.all(:select=> "id,vlan_number", :joins => :logical_interfaces)

Pop_vlan.all(:select => "id,vlan_number", :conditions => ["id not in (?)", subQuery])
epsilones
  • 11,279
  • 21
  • 61
  • 85
  • get an error `Unknown column 'pop_vlans.logical_interface_id'` which is strange, there's no `logical_interface_id` in the `pop_vlans` table, instead, it's primary key is the foreign key in `logical_interfaces` – martincarlin87 Aug 13 '13 at 18:44
  • in fact `Model.all(:joins => :table)` compiles into `SELECT * FROM "model_table" INNER JOIN "table" ON "model_table".id = "table".model_table_id` – epsilones Aug 13 '13 at 19:07
  • so I should swap the the code around so that it's the opposite? – martincarlin87 Aug 13 '13 at 19:36
0

Just incase it helps anyone, I was able to achieve this by doing:

@vlan_numbers = ActiveRecord::Base.connection.execute("SELECT pop_vlans.id, vlan_number FROM pop_vlans WHERE (pop_id = '" + @pop_id.to_s + "' AND vlan_number = '" + @vlan_number.to_s + "') OR (pop_id = '" + @pop_id.to_s + "' AND vlan_number = 'Untagged') OR pop_vlans.id NOT IN (SELECT logical_interfaces.vlan_id FROM logical_interfaces) AND pop_id = '" + @pop_id.to_s + "'")
martincarlin87
  • 10,848
  • 24
  • 98
  • 145