CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-2
martincarlin87
martincarlin87

Reputation: 11062

How to find which foreign keys are referenced

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.

Upvotes: 0

Views: 142

Answers (2)

martincarlin87
martincarlin87

Reputation: 11062

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 + "'")

Upvotes: 0

epsilones
epsilones

Reputation: 11627

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])

Upvotes: 1

Related Questions